poi 和jxl导出excel（2）-白红宇

poi 和jxl导出excel（2）

阅读量：5173 次

发布时间：2019-06-13

本文共 17006 字，大约阅读时间需要 56 分钟。

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
     
       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

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;@Servicepublic 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去重                  * @param list                  * @param param_list                  * @return                  */                public String[] RemoveDuplicate(List
       
        > list,List
        
          param_list) {                    List
         
          
           > listMap = new ArrayList
           
            
             >(); Set
              setMap = new HashSet(); for (Map
               
                 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
                
                  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 {}}