jxl操作excel
/**
* 分隔符
*/
private final static String SEPARATOR = "|"; /**
* 由List导出至指定的Sheet,带total行(最后一行)
* @param wb 模板的workbook
* @param sheetNum 第几个表单
* @param targetFilePath 生成文件夹路径
* @param l 内容list集合,以|分割的对象string集合
* @param headInfoRows 头信息的行数
* @param columnsLength 列数
* @param remarkRowNumber 备注所在行
* @param remark 备注
* @return
* @throws WriteException
* @throws IOException
* int
*/
public static int exportExcelFromList(jxl.Workbook wb, int sheetNum,
String targetFilePath, List<String> l, int headInfoRows,
int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException {
// 创建可写入的Excel工作薄对象
WritableWorkbook wwb = null;
int writeCount = 0; // 单元格样式
// WritableFont bold = new
// WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
WritableCellFormat normalFormat = new WritableCellFormat(
NumberFormats.TEXT);
normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
jxl.format.Colour.BLACK); //设置字体;
WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
WritableCellFormat normalFormat_total = new WritableCellFormat(
font);
normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN,
jxl.format.Colour.BLACK); try { // 创建可写入的Excel工作薄对象
wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb);
WritableSheet ws = wwb.getSheet(0); Label cellRemark = new Label(0, remarkRowNumber, remark,
normalFormat);
ws.addCell(cellRemark); int row = l.size();
int columns = columnsLength;
String[] ary = new String[120]; for (int i = 0; i < row; i++) {
ary = l.get(i).split("\\" + SEPARATOR);
for (int j = 0; j < columns; j++) { if(i==row-1)
{
Label cell = new Label(j, i + headInfoRows, ary[j],
normalFormat_total);
ws.addCell(cell);
}else
{
Label cell = new Label(j, i + headInfoRows, ary[j],
normalFormat);
ws.addCell(cell);
}
}
writeCount++;
}
wwb.write();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (wwb != null) {
wwb.close();
} } return writeCount; } /**
* 导出不需要合计行
* @param wb
* @param sheetNum
* @param targetFilePath
* @param l
* @param headInfoRows
* @param columnsLength
* @param remarkRowNumber
* @param remark
* @return
* @throws WriteException
* @throws IOException
*/
public static int exportExcelFromListNoTotal(jxl.Workbook wb, int sheetNum,
String targetFilePath, List<String> l, int headInfoRows,
int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException {
// 创建可写入的Excel工作薄对象
WritableWorkbook wwb = null;
int writeCount = 0; // 单元格样式
// WritableFont bold = new
// WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
WritableCellFormat normalFormat = new WritableCellFormat(
NumberFormats.TEXT);
normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
jxl.format.Colour.BLACK); //设置字体;
WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat normalFormat_total = new WritableCellFormat(
font);
normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN,
jxl.format.Colour.BLACK); try { // 创建可写入的Excel工作薄对象
wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb);
WritableSheet ws = wwb.getSheet(0); Label cellRemark = new Label(0, remarkRowNumber, remark,
normalFormat);
ws.addCell(cellRemark); int row = l.size();
int columns = columnsLength;
String[] ary = new String[120]; for (int i = 0; i < row; i++) {
ary = l.get(i).split("\\" + SEPARATOR);
for (int j = 0; j < columns; j++) { Label cell = new Label(j, i + headInfoRows, ary[j],
normalFormat);
ws.addCell(cell);
}
writeCount++;
}
wwb.write();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (wwb != null) {
wwb.close();
} } return writeCount; }
exportList为List<String>，生成方式为遍历每个对象并将所有属性以|串起来
List<DetectorHistory> dfList = service.getList(); //获取对象集合
List<String> exportList = new ArrayList<String>();
StringBuffer sbList = new StringBuffer();
if (dfList!=null&&dfList.size()>0) {
for (DetectorHistory ele:dfList) {
sbList.delete(0, sbList.length());
//加入|
//加入属性
...
exportList.add(sbList.toString());
}
}controller层
//模板所在文件夹路径
String tempPath = req.getSession().getServletContext()
.getRealPath(CommonValue.FileTemplatePath);
//生成文件所在文件夹路径
String exportFilePath = req.getSession().getServletContext()
.getRealPath(CommonValue.ExportFilePath);
//导出文件名
String exportFileName = "";
//模板文件名,事先生成好以及头文件情况
String targetFileName = "ReportTmp_detectorHistory.xls"; //生成的行数
int operatorCount = 0; JSONObject jsonObject = new JSONObject();
try {
// 文件导出
if (exportList.size() > 0) {
//生成文件名
exportFileName = "detectorHistory" + CommonTool.getNowDateStr2()
+ "." + targetFileName.split("\\.")[1];
//利用模板生成Workbook
Workbook rw = jxl.Workbook.getWorkbook(new File(tempPath
+ File.separator + targetFileName)); // 写入备注文件
String remarkInfo = "统计时间:" + CommonTool.getNowDateStr2() + " 金额单位:元"; //rw为模板workbook，0为sheetnum，其次为导出文件路径，exportList为|分割属性的string对象集合,4为头的行数,14为列数,1为备注所在行(从0开始),remarkinfo为备注所在行的信息
operatorCount = ExcelHelper_ChargeSituation.exportExcelFromListNoTotal(rw,
0, exportFilePath + File.separator + exportFileName,
exportList, 4, 14, 1, remarkInfo); } jsonObject.put("operatorCount", operatorCount); //返回操作条数
jsonObject.put("exportFilePath", CommonValue.ExportFilePath
+ File.separator + exportFileName); //返回生成的文件路径 if(jsonObject.get("operatorCount")!=null&&Integer.valueOf(jsonObject.get("operatorCount").toString())<=0)
{//当生成内容条数为0时
jsonObject.put("rtnCode", "404");
}else
{
jsonObject.put("rtnCode", "0");
}
} catch (BiffException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}finally{ } res.resetBuffer();
res.setContentType("text/html;charset=UTF-8");
res.getOutputStream().write(jsonObject.toString().getBytes("utf-8"));
res.getOutputStream().flush();
return null;