如何用Apache POI操作Excel文件
controller
@RequestMapping("assign2/downloadAssignWayConfigHistory.do")public?void?downloadAssignWayConfigHistory(HttpServletResponse?response,
@RequestParam(value?=?"collectName",required?=?false)?String?collectName,
@RequestParam(value?=?"modifyStartTime",required?=?false)?String?modifyStartTime,
@RequestParam(value?=?"modifyEndTime",required?=?false)?String?modifyEndTime){
String?fileName?=?"下载清单";
response.setContentType("application/binary;charset=UTF-8");
try{
ServletOutputStream?out=response.getOutputStream();
try?{
//设置文件头：最后一个参数是设置下载文件名
response.setHeader("Content-Disposition",?"attachment;fileName="?+?URLEncoder.encode(fileName+".xls",?"UTF-8"));
}?catch?(UnsupportedEncodingException?e)?{
e.printStackTrace();
}
String[]?excleTitles?=?null;//Excel第一行标题
ArrayList<Map<String,Object>>?excelDatas?=?new?ArrayList<Map<String,Object>>();//Excel数据
excleTitles?=?new?String[]?{?"标题1",?"标题2",?"标题3",?"标题4",?"标题5","标题6",?"标题7",?"标题8",?"标题9",?"标题10","标题11",?"标题12"};
//获取数据
HashMap<String,?Object>?params?=?new?HashMap<String,?Object>();
if?(StringUtils.isNotBlank(collectName))?{
params.put("collectName",?collectName);
}
if?(StringUtils.isNotBlank(modifyStartTime))?{
params.put("modifyStartTime",?modifyStartTime);
}
if?(StringUtils.isNotBlank(modifyEndTime))?{
params.put("modifyEndTime",?modifyEndTime);
}
Map<String,?Object>?assignCaseWayHistory?=?collectCenterAssignSettingService.getAssignCaseWayHistory(params);
excelDatas=?(ArrayList)?assignCaseWayHistory.get("rows");
//导出Excel
collectCenterAssignSettingService.exportAssignCaseWayHistory(excleTitles,?excelDatas,?out);?
}?catch(Exception?e){
e.printStackTrace();
}
}
service
public?void?exportAssignCaseWayHistory(String[]?exceltitles,ArrayList<Map<String,?Object>>?excelDatas,?OutputStream?out)?{
try{
//?第一步，创建一个workbook，对应一个Excel文件
HSSFWorkbook?workbook?=?new?HSSFWorkbook();
//?第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet?hssfSheet?=?workbook.createSheet("sheet1");
//?第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow?row?=?hssfSheet.createRow(0);
//不带背景颜色居中的cellStyle
HSSFCellStyle?hssfCellStyle?=?workbook.createCellStyle();
//居中
hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
hssfCellStyle.setVerticalAlignment(hssfCellStyle.VERTICAL_CENTER);
//带背景和字体颜色并且居中的cellStyle
HSSFCellStyle?hssfCellStyleWithBackgroundColor?=?workbook.createCellStyle();
//居中
hssfCellStyleWithBackgroundColor.setAlignment(HSSFCellStyle.ALIGN_CENTER);
hssfCellStyleWithBackgroundColor.setVerticalAlignment(hssfCellStyle.VERTICAL_CENTER);
//背景
hssfCellStyleWithBackgroundColor.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
hssfCellStyleWithBackgroundColor.setFillPattern(hssfCellStyle.SOLID_FOREGROUND);
//字体
HSSFFont?font?=?workbook.createFont();
font.setColor(HSSFColor.RED.index);
hssfCellStyleWithBackgroundColor.setFont(font);
HSSFCell?hssfCell?=?null;
for?(int?i?=?0;?i?<?exceltitles.length;?i++)?{
hssfCell?=?row.createCell(i);//列索引从0开始
hssfCell.setCellValue(exceltitles[i]);//列名1
hssfCell.setCellStyle(hssfCellStyle);//列居中显示
//设置列宽
if?(i?==?1?||?i?==?6?||?i?==?7)?{
hssfSheet.setColumnWidth(i,?5000);
}else?{
hssfSheet.setColumnWidth(i,?3000);
}
}
//?第五步，写入实体数据
for?(int?i?=?0;?i?<?excelDatas.size();?i++)?{
row?=?hssfSheet.createRow(2*i+1);?//奇数行
Map<String,?Object>?excelData?=?excelDatas.get(i);
//?第六步，创建单元格，并设置值
String?updateBy?=?(String)?excelData.get("updateBy");
String?updateDate?=?(String)?excelData.get("updateDate");
String?region?=?(String)?excelData.get("region");
String?branch?=?(String)?excelData.get("branch");
String?center?=?(String)?excelData.get("center");
String?dept?=?(String)?excelData.get("dept");
String?assignMode?=?(String)?excelData.get("assignMode");
String?newAssignMode?=?(String)?excelData.get("newAssignMode");
String?turnableDimension?=?(String)?excelData.get("turnableDimension");
String?newTurnableDimension?=?(String)?excelData.get("newTurnableDimension");
String?M0?=?(String)?excelData.get("M0");
String?M1?=?(String)?excelData.get("M1");
String?M2?=?(String)?excelData.get("M2");
String?M3?=?(String)?excelData.get("M3");
String?newM0?=?(String)?excelData.get("newM0");
String?newM1?=?(String)?excelData.get("newM1");
String?newM2?=?(String)?excelData.get("newM2");
String?newM3?=?(String)?excelData.get("newM3");
HSSFCell?cell0?=?row.createCell(0);
cell0.setCellStyle(hssfCellStyle);
cell0.setCellValue(updateBy);
HSSFCell?cell1?=?row.createCell(1);
cell1.setCellStyle(hssfCellStyle);
cell1.setCellValue(updateDate);
HSSFCell?cell2?=?row.createCell(2);
cell2.setCellStyle(hssfCellStyle);
cell2.setCellValue(region);
HSSFCell?cell3?=?row.createCell(3);
cell3.setCellStyle(hssfCellStyle);
cell3.setCellValue(branch);
HSSFCell?cell4?=?row.createCell(4);
cell4.setCellStyle(hssfCellStyle);
cell4.setCellValue(center);
HSSFCell?cell5?=?row.createCell(5);
cell5.setCellStyle(hssfCellStyle);
cell5.setCellValue(dept);
HSSFCell?cell6?=?row.createCell(6);
cell6.setCellStyle(hssfCellStyle);
cell6.setCellValue(assignMode.equals("0")?"模式1":"模式2");
if?("BRANCH".equals(turnableDimension))?{
turnableDimension?=?"分部";
}?else?if?("CENTER".equals(turnableDimension))?{
turnableDimension?=?"中心";
}?else?if?("DEPT".equals(turnableDimension))?{
turnableDimension?=?"部";
}
HSSFCell?cell7?=?row.createCell(7);
cell7.setCellStyle(hssfCellStyle);
cell7.setCellValue(turnableDimension);
HSSFCell?cell8?=?row.createCell(8);
cell8.setCellStyle(hssfCellStyle);
cell8.setCellValue(M0.equalsIgnoreCase("Y")?"案2":"案2");
HSSFCell?cell9?=?row.createCell(9);
cell9.setCellStyle(hssfCellStyle);
cell9.setCellValue(M1.equalsIgnoreCase("Y")?"案1":"案2");
HSSFCell?cell10?=?row.createCell(10);
cell10.setCellStyle(hssfCellStyle);
cell10.setCellValue(M2.equalsIgnoreCase("Y")?"案1":"案2");
HSSFCell?cell11?=?row.createCell(11);
cell11.setCellStyle(hssfCellStyle);
cell11.setCellValue(M3.equalsIgnoreCase("Y")?"案1":"案2");
row?=?hssfSheet.createRow(2*i+2);?//偶数行?
HSSFCell?newCell6?=?row.createCell(6);
newCell6.setCellValue(newAssignMode.equals("0")?"模式1":"模式2");//设置值
if?(!newAssignMode.equals(assignMode)){//设置背景色
newCell6.setCellStyle(hssfCellStyleWithBackgroundColor);
}else?{
newCell6.setCellStyle(hssfCellStyle);
}
if?("BRANCH".equals(newTurnableDimension))?{
newTurnableDimension?=?"haha";
}?else?if?("CENTER".equals(newTurnableDimension))?{
newTurnableDimension?=?"hehe";
}?else?if?("DEPT".equals(newTurnableDimension))?{
newTurnableDimension?=?"hiahia";
}
HSSFCell?newCell7?=?row.createCell(7);
newCell7.setCellValue(newTurnableDimension);
if?(!newTurnableDimension.equals(turnableDimension)){//设置背景色
newCell7.setCellStyle(hssfCellStyleWithBackgroundColor);
}else?{
newCell7.setCellStyle(hssfCellStyle);
}
HSSFCell?newCell8?=?row.createCell(8);
newCell8.setCellValue(newM0.equalsIgnoreCase("Y")?"案1":"案2");
if?(!newM0.equals(M0)){//设置背景色
newCell8.setCellStyle(hssfCellStyleWithBackgroundColor);
}else?{
newCell8.setCellStyle(hssfCellStyle);
}
HSSFCell?newCell9?=?row.createCell(9);
newCell9.setCellValue(newM1.equalsIgnoreCase("Y")?"案1":"案2");
if?(!newM1.equals(M1)){//设置背景色
newCell9.setCellStyle(hssfCellStyleWithBackgroundColor);
}else?{
newCell9.setCellStyle(hssfCellStyle);
}
HSSFCell?newCell10?=?row.createCell(10);
newCell10.setCellValue(newM2.equalsIgnoreCase("Y")?"案1":"案2");
if?(!newM2.equals(M2)){//设置背景色
newCell10.setCellStyle(hssfCellStyleWithBackgroundColor);
}else?{
newCell10.setCellStyle(hssfCellStyle);
}
HSSFCell?newCell11?=?row.createCell(11);
newCell11.setCellValue(newM3.equalsIgnoreCase("Y")?"案1":"案2");
if?(!newM3.equals(M3)){//设置背景色
newCell11.setCellStyle(hssfCellStyleWithBackgroundColor);
}else?{
newCell11.setCellStyle(hssfCellStyle);
}
//合并单元格
for?(int?j?=?0;?j?<?6;?j++){
CellRangeAddress?cellRangeAddress?=?new?CellRangeAddress(2*i+1,?2*i+2,?j,?j);
hssfSheet.addMergedRegion(cellRangeAddress);
}
}
//?第七步，将文件输出到客户端浏览器
try?{
workbook.write(out);
out.flush();
out.close();
}?catch?(Exception?e)?{
e.printStackTrace();
}
}catch(Exception?e){
e.printStackTrace();
}
}