【www.bbyears.com--excel】
正常导出报表的场景就是从数据库读取数据,然后按照指定的格式生成报表。其中可能涉及到的就是插入/复制行,单元格设置公式计算这些典型应用。下面就按这个需求给出解决方案。思路就是,首先制定一个Excel模板文档,暂命名为TEMPLATE.xls;然后读取该文档,插入/复制一些数据进去,另存为新的报表文件。这样就不需要在写入数据的时候考虑繁琐的表格样式问题了。
读取模板文件
代码如下 复制代码
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(TEMPLATE_PATH));
HSSFWorkbook wb = new HSSFWorkbook(fs, true);
HSSFSheet sheet = wb.getSheet("Sheet1");
复制行
代码如下 复制代码
public void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) {
// Get the source / new row
HSSFRow newRow = worksheet.getRow(destinationRowNum);
HSSFRow sourceRow = worksheet.getRow(sourceRowNum);
// If the row exist in destination, push down all rows by 1 else create
// a new row
if (newRow != null) {
worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
} else {
newRow = worksheet.createRow(destinationRowNum);
}
// Loop through source columns to add to new row
for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
// Grab a copy of the old/new cell
HSSFCell oldCell = sourceRow.getCell(i);
HSSFCell newCell = newRow.createCell(i);
// If the old cell is null jump to next cell
if (oldCell == null) {
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
HSSFCellStyle newCellStyle = workbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
newCell.setCellStyle(newCellStyle);
// If there is a cell comment, copy
if (newCell.getCellComment() != null) {
newCell.setCellComment(oldCell.getCellComment());
}
// If there is a cell hyperlink, copy
if (oldCell.getHyperlink() != null) {
newCell.setHyperlink(oldCell.getHyperlink());
}
// Set the cell data type
newCell.setCellType(oldCell.getCellType());
// Set the cell data value
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
worksheet.addMergedRegion(newCellRangeAddress);
}
}
}
插入行,并设置相同的样式
代码如下 复制代码
// Insert one row
sheet.shiftRows(rowNumber, sheet.getLastRowNum(), 1);
HSSFRow source = sheet.getRow(rowNumber - 1);
HSSFRow newRow = sheet.createRow(rowNumber);
for (int i = source.getFirstCellNum(), columnNo = source.getLastCellNum(); i < columnNo; i++) {
HSSFCell sourceCell = source.getCell(i);
HSSFCell targetCell = newRow.createCell(i);
targetCell.setCellStyle(sourceCell.getCellStyle());
//Get data from DB and set cell value
targetCell.setCellValue("Just for test " + i);
}
设置单元格公式的时候,注意不要加=,直接设置公式就可以。
代码如下 复制代码//设置单元格公式
sheet.getRow(total).getCell(10).setCellFormula(String.format("SUM(K%s:K%s)", aIndex + 1, total));
sheet.getRow(total).getCell(11).setCellFormula(String.format("100*J%s/K%s", total + 1, total + 1));
//列宽度自适应
sheet.autoSizeColumn(1);
保存
代码如下 复制代码
FileOutputStream fileOut = new FileOutputStream(SAVE_PATH);
wb.write(fileOut);
fileOut.close();
update @ 2011/11/09
删除行,行号0-based
代码如下 复制代码 public void removeRow(HSSFSheet sheet, int rowIndex) {int lastRowNum = sheet.getLastRowNum();
if (rowIndex >= 0 && rowIndex < lastRowNum) {
sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
}
if (rowIndex == lastRowNum) {
HSSFRow removingRow = sheet.getRow(rowIndex);
if (removingRow != null) {
sheet.removeRow(removingRow);
}
}
}
这里只是列出了一些简单操作
公司用的一个导出Excel表格的类,贴出来和大家共享下
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;
import com.shxy.exception.FileOperationException;
import com.shxy.util.Verify;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* 生成excel表格
* @author
*
*/
public class ExcelExport {
/**
* 构造器
*
*/
public ExcelExport() {
}
/**
* 生成具有一定格式excel
* @param sheetName sheet名称,默认为sheet1
* @param nf 数字类型的格式 如:jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");默认无格式
* @param content 二维数组,要生成excel的数据来源
* @param 合并项 每一项的数据格式为0,1,0,2 即:把(0,1)和(0,2)合并--->第1列的第一、二个元素合并
* @param os excel输出流
* @param row 需要水平居中的行,默认居左。以逗号分隔的字符串
* @param col 需要水平居中的列,默认居左。以逗号分隔的字符串
* @throws FileOperationException
*/
public void export(String sheetName, NumberFormat nf, String[][] content, String[] mergeInfo, OutputStream os, String row, String col)
throws FileOperationException {
if (Verify.isNullObject(content, os)
|| Verify.isNull2DArray(content)) {
return;
}
//默认名称
if(Verify.isNullObject(sheetName)){
sheetName="sheet1";
}
Set
Set
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if(content[i][j]==null){
content[i][j]="";
}
if (isNumber(content[i][j])&&!rows.contains(i)&&!cols.contains(j)) {//处理数字
Number number=null;
if(Verify.isNullObject(nf)){//数字无格式
number = new Number(j, i, Double
.valueOf(content[i][j]));
}else{//如果有格式,按格式生成
jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat(nf);
number = new Number(j, i, Double
.valueOf(content[i][j]),wcfn);
}
sheet.addCell(number);
} else {//处理非数字
WritableCellFormat format=new WritableCellFormat();
if(rows.contains(i)||cols.contains(j)){
format.setAlignment(jxl.format.Alignment.CENTRE);
}else{
format.setAlignment(jxl.format.Alignment.LEFT);
}
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, i, content[i][j],format);
sheet.addCell(label);
}
}
}
this.merge(sheet, mergeInfo);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
throw new FileOperationException(e);
}finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
* @param sheetName sheet名称,默认为sheet1
* @param content 二维数组,要生成excel的数据来源
* @param os excel输出流
* @throws FileOperationException
*/
public void exportFormatExcel(String[][] content, String sheetName, OutputStream os)
throws FileOperationException {
if (Verify.isNullObject(content, os)
|| Verify.isNull2DArray(content)) {
return;
}
// 默认名称
if(Verify.isNullObject(sheetName)){
sheetName="sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
for (int i = 0; i < content.length; i++) {
for (int j = 0; j < content[i].length; j++) {
if(content[i][j]==null){
content[i][j]="";
}
WritableCellFormat format=new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, i, content[i][j],format);
sheet.addCell(label);
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
throw new FileOperationException(e);
}finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
* @param sheetName sheet名称,默认为sheet1
* @param content Map,要生成excel的数据来源
* @param os excel输出流
* @throws FileOperationException
*/
public void exportFormatExcel(Map
throws FileOperationException {
if (Verify.isNullObject(content, os)
|| content.size()==0) {
return;
}
// 默认名称
if(Verify.isNullObject(sheetName)){
sheetName="sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
int index = 0;
for (int k = 0; k < salary_name_array.length; k++) {
String[][] value = (String[][])content.get(salary_name_array[k]);
if(value!=null&&value.length>0){
if(index!=0){
index++;
}
WritableCellFormat format1=new WritableCellFormat();
format1.setAlignment(jxl.format.Alignment.LEFT);
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label1 = new Label(0, index, salary_name_array[k] ,format1);
sheet.addCell(label1);
for (int i = 0; i < value.length; i++) {
index ++ ;
for (int j = 0; j < value[i].length; j++) {
if(value[i][j]==null){
value[i][j]="";
}
WritableCellFormat format=new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, index, value[i][j],format);
sheet.addCell(label);
}
}
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
throw new FileOperationException(e);
}finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 合并表格
* @param sheet 工作表
* @param mergeInfo 要合并的表格的信息
* @throws RowsExceededException
* @throws NumberFormatException
* @throws WriteException
*/
private void merge(WritableSheet sheet,String[]mergeInfo) throws RowsExceededException, NumberFormatException, WriteException{
if(Verify.isNullObject(sheet)||Verify.isNull1DArray(mergeInfo)){
return;
}else if(!this.isMergeInfo(mergeInfo)){
return;
}else{
for(String str:mergeInfo){
String[]temp=str.split(",");
sheet.mergeCells(Integer.parseInt(temp[1]), Integer.parseInt(temp[0]), Integer.parseInt(temp[3]), Integer.parseInt(temp[2]));
}
}
}
/**
* 处理要居中的行或列的数据
* @param indexes 行标或列标
* @return 行坐标或列坐标组成的集合
*/
private Set
Set
if(Verify.isNullObject(indexes)){
return set;
}
String[]temp=indexes.split(",",0);
for(String str:temp){
if(isNumeric(str)){
set.add(Integer.parseInt(str));
}
}
return set;
}
/**
* 判断字符串是否由纯数字组成
* @param str 源字符串
* @return true是,false否
*/
private boolean isNumeric(String str) {
if (Verify.isNullObject(str)){
return false;
}
Pattern pattern = Pattern.compile("[0-9]*");
return pattern.matcher(str).matches();
}
/**
* 判断字符串是否是数字
* @param str 源字符串
* @return true是,false否
*/
private boolean isNumber(String number) {
//判断参数
if (Verify.isNullObject(number)){
return false;
}
//查看是否有小数点
int index = number.indexOf(".");
if (index < 0) {
return isNumeric(number);
} else {
//如果有多个".",则不是数字
if(number.indexOf(".")!=number.lastIndexOf(".")){
return false;
}
String num1 = number.substring(0, index);
String num2 = number.substring(index + 1);
return isNumeric(num1) && isNumeric(num2);
}
}
/**
* 判断合并项内容是否合法
* @param mergeInfo 合并项 每一项的数据格式为0,1,0,2即把(0,1)和(0,2)合并
* @return true合法,false非法
*/
private boolean isMergeInfo(String[]mergeInfo){
if(Verify.isNull1DArray(mergeInfo)){
return false;
}else{
for(String str:mergeInfo){
String[]temp=str.split(",");
if(Verify.isNull1DArray(temp)||temp.length!=4){
return false;
}else{
for(String s:temp){
if(!isNumeric(s)){
return false;
}
}
}
}
}
return true;
}
public static void main(String[] args) {
ExcelExport ee=new ExcelExport();
String[][]content=new String[][]{{"测试","第一列",null,"第三列"},{"第一行","aa","2.00","22"},{"第二行","bb","3.01","3"},{"第三行","cc","4.00","4"}};
try {
OutputStream os=new FileOutputStream("D://test//test.xls");
// ee.export(null,null, content,null, os);
ee.export(null,null, content,new String[]{"0,1,0,2","1,0,3,0"}, os,"0,1","0");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}