poi 和jxl导出excel（2）
controller：
/** * 导出报表 * @return */ @RequestMapping(value = "/export") @ResponseBody public void export(HttpServletRequest request,HttpServletResponse response, String id, String auditno) throws Exception { /*jxl String headers = ""; String[] array = addEmpService.returnStringArray(id); jxl.createTable(headers, array, "F:\\qq"+auditno+".xls");*/ //获取数据 List<EmpAuditDetails> list = addEmpService.queryDetailsById(id); //excel标题 String[] title = {"姓名","身份证号码","性别","出生日期","保险缴费基数","工种","增员年月","增员原因","手机号","紧急联系人","紧急联系电话","家庭住址","通讯地址省级","通讯地址市级","通讯地址区县级","通讯地址补充信息","户口所在地省级","户口所在地市级","户口所在地区县级","户口所在地补充信息"}; //excel文件名 String fileName = auditno+".xls"; //sheet名 String sheetName = auditno; String [][] content = new String[list.size()][]; for (int i = 0; i < list.size(); i++) { content[i] = new String[title.length]; content[i][0] = list.get(i).getName(); content[i][1] = list.get(i).getIdno(); content[i][2] = list.get(i).getSex(); content[i][3] = list.get(i).getBirth(); content[i][4] = String.valueOf(list.get(i).getInsuranceBase()); content[i][5] = list.get(i).getJob(); content[i][6] = DateUtils.convertDateToString(list.get(i).getAddYm(),"yyyy-MM-dd"); content[i][7] = list.get(i).getAddReason(); content[i][8] = list.get(i).getPhone(); content[i][9] = list.get(i).getLink(); content[i][10] = list.get(i).getLinkphone(); content[i][11] = list.get(i).getAddress(); content[i][12] = list.get(i).getProvince(); content[i][13] = list.get(i).getCity(); content[i][14] = list.get(i).getDistrict(); content[i][15] = list.get(i).getAddressExtra(); content[i][16] = list.get(i).getNativeProvince(); content[i][17] = list.get(i).getNativeCity(); content[i][18] = list.get(i).getNativeDistrict(); content[i][19] = list.get(i).getNativeExtra(); } //创建HSSFWorkbook HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null); //响应到客户端 try { ResponseUtil.setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); wb.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } }
excelutil
package com.lf.common.utils; import java.sql.ResultSet; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; public class ExcelUtil { /** * 导出Excel * @param sheetName sheet名称 * @param title 标题 * @param values 内容 * @param wb HSSFWorkbook对象 * @return */ public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){ // 第一步，创建一个HSSFWorkbook，对应一个Excel文件 if(wb == null){ wb = new HSSFWorkbook(); } // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet(sheetName); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 HSSFRow row = sheet.createRow(0); // 第四步，创建单元格，并设置值表头 设置表头居中 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font=(HSSFFont) wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font);// 创建一个居中格式 //声明列对象 HSSFCell cell = null; //创建标题 for(int i=0;i<title.length;i++){ cell = row.createCell(i); cell.setCellValue(title[i]); cell.setCellStyle(style); } //创建内容 for(int i=0;i<values.length;i++){ row = sheet.createRow(i + 1); for(int j=0;j<values[i].length;j++){ //将内容按顺序赋给对应的列对象 row.createCell(j).setCellValue(values[i][j]); } } return wb; } }
ResponseUtil：
package com.lf.common.utils; import org.apache.poi.ss.usermodel.Workbook; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.io.PrintWriter; import java.io.UnsupportedEncodingException; public class ResponseUtil { public static void write(HttpServletResponse response, Object o) throws Exception { response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); out.println(o.toString()); out.flush(); out.close(); } public static void export(HttpServletResponse response, Workbook wb, String fileName) throws Exception{ //设置头 固定格式 response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "iso8859-1")); response.setContentType("text/html;charset=utf-8"); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); } //发送响应流方法 public static void setResponseHeader(HttpServletResponse response, String fileName) { try { try { fileName = new String(fileName.getBytes(),"ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setContentType("application/octet-stream;charset=ISO8859-1"); response.setHeader("Content-Disposition", "attachment;filename="+ fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } }
jxl：
package com.lf.common.utils; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.Date; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set; import org.springframework.stereotype.Service; import com.lf.utils.DateUtils; import jxl.CellView; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.UnderlineStyle; import jxl.read.biff.BiffException; import jxl.write.Border; import jxl.write.BorderLineStyle; import jxl.write.Colour; import jxl.write.Label; import jxl.write.NumberFormats; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; @Service public class JxlWriteExcel { public boolean createTable(String header, String[] body, String filePath) { boolean createFlag = true; WritableWorkbook book; try { // 根据路径生成excel文件 book = Workbook.createWorkbook(new File(filePath)); // 创建一个sheet名为"表格" WritableSheet sheet = book.createSheet("表格", 0); // 设置NO列宽度 // 去掉整个sheet中的网格线 sheet.getSettings().setShowGridLines(false); Label tempLabel = null; // 表头输出 String[] headerArr = header.split(","); int headerLen = headerArr.length; // 循环写入表头内容 for (int i = 0; i < headerLen; i++) { tempLabel = new Label( i, 0, headerArr[i], getHeaderCellStyle()); sheet.addCell(tempLabel); sheet.setColumnView(i, 30); } // 表体输出 int bodyLen = body.length; // 循环写入表体内容 for (int j = 0; j < bodyLen; j++) { String[] bodyTempArr = body[j].split(","); for (int k = 0; k < bodyTempArr.length; k++) { WritableCellFormat tempCellFormat = null; tempCellFormat = getBodyCellStyle(); if (tempCellFormat != null) { if (k == 0 || k == (bodyTempArr.length - 1)) { tempCellFormat.setAlignment(Alignment.CENTRE); } } tempLabel = new Label( k, 1 + j, bodyTempArr[k], tempCellFormat); sheet.addCell(tempLabel); } } book.write(); book.close(); } catch (IOException e) { createFlag = false; System.out.println("EXCEL创建失败！"); e.printStackTrace(); } catch (RowsExceededException e) { createFlag = false; System.out.println("EXCEL单元设置创建失败！"); e.printStackTrace(); } catch (WriteException e) { createFlag = false; System.out.println("EXCEL写入失败！"); e.printStackTrace(); } return createFlag; } public boolean createErroeTable(String header, String[] body, String filePath ,String sheetName,int sheetNum) { boolean createFlag = true; WritableWorkbook book ; WritableSheet sheet = null ; File errorFile = new File(filePath); try { if (errorFile.exists()) { Workbook workbook = Workbook.getWorkbook(errorFile); book = workbook.createWorkbook(errorFile,workbook); sheet = book.createSheet(sheetName, 1); }else { // 根据路径生成excel文件 book = Workbook.createWorkbook(errorFile); // 创建一个sheet名为"表格" sheet = book.createSheet(sheetName, sheetNum); } // 设置NO列宽度 // 去掉整个sheet中的网格线 sheet.getSettings().setShowGridLines(false); Label tempLabel = null; // 表头输出 String[] headerArr = header.split(","); int headerLen = headerArr.length; // 循环写入表头内容 for (int i = 0; i < headerLen; i++) { tempLabel = new Label( i, 0, headerArr[i], getHeaderCellStyle()); sheet.addCell(tempLabel); sheet.setColumnView(i, 30); } // 表体输出 int bodyLen = body.length; // 循环写入表体内容 for (int j = 0; j < bodyLen; j++) { String[] bodyTempArr = body[j].split(","); for (int k = 0; k < bodyTempArr.length; k++) { WritableCellFormat tempCellFormat = null; tempCellFormat = getBodyCellStyle(); if (tempCellFormat != null) { if (k == 0 || k == (bodyTempArr.length - 1)) { tempCellFormat.setAlignment(Alignment.CENTRE); } } tempLabel = new Label( k, 1 + j, bodyTempArr[k], tempCellFormat); sheet.addCell(tempLabel); } } book.write(); book.close(); } catch (IOException e) { createFlag = false; System.out.println("EXCEL创建失败！"); e.printStackTrace(); } catch (RowsExceededException e) { createFlag = false; System.out.println("EXCEL单元设置创建失败！"); e.printStackTrace(); } catch (WriteException e) { createFlag = false; System.out.println("EXCEL写入失败！"); e.printStackTrace(); } catch (BiffException e) { System.out.println("读取EXCEL对象失败！"); e.printStackTrace(); } return createFlag; } public WritableCellFormat getHeaderCellStyle() { WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE); WritableCellFormat headerFormat = new WritableCellFormat( NumberFormats.TEXT); try { // 添加字体设置 headerFormat.setFont(font); // 设置单元格背景色：表头为黄色 headerFormat.setBackground(Colour.YELLOW); // 设置表头表格边框样式 // 整个表格线为粗线、黑色 headerFormat.setBorder(Border.ALL, BorderLineStyle.THICK, Colour.BLACK); // 表头内容水平居中显示 headerFormat.setAlignment(Alignment.CENTRE); } catch (WriteException e) { System.out.println("表头单元格样式设置失败！"); } return headerFormat; } public WritableCellFormat getBodyCellStyle() { WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE); WritableCellFormat bodyFormat = new WritableCellFormat(font); try { // 设置单元格背景色：表体为白色 bodyFormat.setBackground(Colour.WHITE); // 设置表头表格边框样式 // 整个表格线为细线、黑色 bodyFormat .setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); } catch (WriteException e) { System.out.println("表体单元格样式设置失败！"); } return bodyFormat; } public void deleteFile(String fileName){ File file = new File(fileName); if(file.exists()){ file.delete(); System.out.println("删除文件成功"); }else{ System.out.println("文件不存在"); } } /** * 创建表格 * @module： * @author: ZhangK * @date： Aug 1, 2016 */ public String createTableByZk(String header, String[] body, String filePath) { WritableWorkbook book; String date = DateUtils.convertDateToString(new Date(), "yyyyMMddHHmmssSSS"); String fileName=date+".xls"; File file = null; if (fileName !=null|| "".equals(fileName)) { file = new File(fileName); }else{ return null; } try { // 根据路径生成excel文件 book = Workbook.createWorkbook(file); // 创建一个sheet名为"表格" WritableSheet sheet = book.createSheet("发货情况", 0); // 去掉整个sheet中的网格线 sheet.getSettings().setShowGridLines(false); Label tempLabel = null; // 表头输出 String[] headerArr = header.split(","); int headerLen = headerArr.length; // 循环写入表头内容 CellView cv = null; for (int i = 0; i < headerLen; i++) { tempLabel = new Label(i, 0, headerArr[i], getHeaderCellStyle()); sheet.addCell(tempLabel); // 设置NO列宽度 sheet.setColumnView(i, 15); } // 表体输出 int bodyLen = body.length; // 循环写入表体内容 for (int j = 0; j < bodyLen; j++) { String[] bodyTempArr = body[j].split(","); for (int k = 0; k < bodyTempArr.length; k++) { WritableCellFormat tempCellFormat = null; tempCellFormat = getBodyCellStyle(); if (tempCellFormat != null) { if (k == 0 || k == (bodyTempArr.length - 1)) { tempCellFormat.setAlignment(Alignment.CENTRE); } } tempLabel = new Label( k, 1 + j, bodyTempArr[k], tempCellFormat); sheet.addCell(tempLabel); } } book.write(); book.close(); } catch (IOException e) { fileName = null; System.out.println("EXCEL创建失败！"); e.printStackTrace(); } catch (RowsExceededException e) { fileName = null; System.out.println("EXCEL单元设置创建失败！"); e.printStackTrace(); } catch (WriteException e) { fileName = null; System.out.println("EXCEL写入失败！"); e.printStackTrace(); } return fileName; } /** * list<map>去重 * @param list * @param param_list * @return */ public String[] RemoveDuplicate(List<Map<String, String>> list,List<String> param_list) { List<Map<String, String>> listMap = new ArrayList<Map<String, String>>(); Set<Map> setMap = new HashSet<Map>(); for (Map<String, String> map1 : list) { if (setMap.add(map1)) { listMap.add(map1); } } String[] rowData_retail = new String[listMap.size()]; for (int i = 0; i < listMap.size(); i++) { Map<String, String> map = listMap.get(i); for (int j = 0; j < param_list.size(); j++) { String param_i = map.get(param_list.get(j)); param_i = map.get(param_list.get(j)) == null ? "" : map .get(param_list.get(j)); if (j == param_list.size() -1) { rowData_retail[i] = rowData_retail[i] + param_i ; }else if (j==0) { rowData_retail[i] = param_i + ","; }else { rowData_retail[i] = rowData_retail[i] + param_i + ","; } } } return rowData_retail; } public static void main(String[] args) throws IOException {} }
