Excel 工具类 备选

java

浏览数:86

2019-1-13

片段 1片段 2


ExcelUtil.java

package com.eifini.ew.util;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.swing.plaf.synth.Region;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;

public class  ExcelUtil {
    //excel默认宽度;  自动调整列宽
    private static int width = -1;  
    //默认字体  
    private static String excelfont = "微软雅黑";  
    
    
    /** 
     *  
     * 获取单元格里的数字,正常返回数字,有错返回 E
     *  
     * @param cell 
     * @return 
     */  
    public static String getCellFormatValue(Cell cell) {  
        String cellvalue = "";  
        if (cell != null) {
            // 判断当前Cell的Type  
            switch (cell.getCellType()) {  
            case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC  
            case Cell.CELL_TYPE_FORMULA: {  
                // 判断当前的cell是否为Date  
                if (DateUtil.isCellDateFormatted(cell)) {  
                    cellvalue = EnterpriseConstant.ERROR;
                } else {// 如果是纯数字  
                    // 取得当前Cell的数值  
                    cellvalue = String.valueOf(cell.getNumericCellValue());  
                }  
                break;
            }  
            case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING  
                // 取得当前的Cell字符串  
                cellvalue = cell.getRichStringCellValue().getString();  
                break;  
            default:// 默认的Cell值  
                cellvalue = EnterpriseConstant.ERROR;  
            }  
        } else {  
            cellvalue = "";  
        }  
        return cellvalue;  
    }
    /** 
     *  
     * 获取每一行第一列的值
     *  
     * @param cell 
     * @return 
     */  
    public static String getRowFistCellValue(Row row) {
        if(row==null){
            return "";
        }
        // 单元格
        Cell cell = row.getCell(0);
        return getCellFormatValue(cell);
    }

    /** 
     *  
     * @param excelName  导出的EXCEL名字 
     * @param sheetName  导出的SHEET名字  当前sheet数目只为1 
     * @param headers      导出的表格的表头 
     * @param ds_titles      导出的数据 map.get(key) 对应的 key 
     * @param ds_format    导出数据的样式 
     *                          1:String left;  
     *                          2:String center    
     *                          3:String right 
     *                          4 int  right 
     *                          5:float ###,###.## right  
     *                          6:number: #.00% 百分比 right 
     *                          101:date: yyyy-MM-dd left
     * @param widths      表格的列宽度  默认为 256*14 
     * @param data        数据集  List<Map> 
     * @param response 
     * @throws IOException 
     */  
    public static void export(String excelName, String sheetName,String[] headers,String[] ds_titles,int[] ds_format,int[] widths, List<Map<String,Object>> data ,HttpServletRequest request, HttpServletResponse response) throws IOException {  
        HttpSession session = request.getSession();    
        session.setAttribute("state", null);    
           //设置文件名  
            String fileName = "";  
            if(StringUtils.isNotEmpty(excelName)){  
                fileName = excelName;  
            }  
            //创建一个工作薄  
            HSSFWorkbook wb = new HSSFWorkbook();  
            //创建sheet
            createSheet(wb,sheetName, headers, ds_titles,ds_format, widths,  data,null);
            
            fileName=fileName+".xls";  
            String filename = "";  
            try{  
               filename =encodeChineseDownloadFileName(request,fileName);  
            }catch(Exception e){  
                e.printStackTrace();  
            }  
//          final String userAgent = request.getHeader("USER-AGENT");  
//            if(userAgent.indexOf( "MSIE")!=-1){//IE浏览器  
//              filename = URLEncoder.encode(fileName,"UTF8");  
//            }else if(userAgent.indexOf( "Mozilla")!=-1){//google,火狐浏览器  
//              filename = new String(fileName.getBytes(), "ISO8859-1");  
//            }else{  
//              filename = URLEncoder.encode(fileName,"UTF8");//其他浏览器  
//            }  
              
            response.setHeader("Content-disposition", filename);  
            response.setContentType("application/vnd.ms-excel");    
            response.setHeader("Content-disposition", "attachment;filename="+filename);    
            response.setHeader("Pragma", "No-cache");  
            OutputStream ouputStream = response.getOutputStream();    
            wb.write(ouputStream);    
            ouputStream.flush();    
            ouputStream.close();  
            session.setAttribute("state", "open");  
              
    }  

    /** 
     *  
     *  导出Excel  多个sheet 
     * @param excelName  导出的EXCEL名字 
     * @param sheetMapList  sheet集合 ,里面包含
     * 
                                     * @param headers      导出的表格的表头 
                                     * @param ds_titles      导出的数据 map.get(key) 对应的 key 
                                     * @param ds_format    导出数据的样式 
                                     *                          1:String left;  
                                     *                          2:String center    
                                     *                          3:String right 
                                     *                          4 int  right 
                                     *                          5:float ###,###.## right  
                                     *                          6:number: #.00% 百分比 right 
                                     *                          101:date: yyyy-MM-dd left
                                     * @param widths      表格的列宽度  默认为 256*14 
                                     * @param data        数据集  List<Map> 
        
     * @param response 
     * @throws IOException 
     */  
    public static void exportMultiSheet(String excelName,List<Map> sheetMapList,HttpServletRequest request,HttpServletResponse response) throws IOException { 
        HttpSession session = request.getSession();    
        session.setAttribute("state", null);    
           //设置文件名  
            String fileName = "";  
            if(StringUtils.isNotEmpty(excelName)){  
                fileName = excelName;  
            }  
            //创建一个工作薄  
            HSSFWorkbook wb = new HSSFWorkbook();
            //循环创建SHEET
            for(Map sheetMap:sheetMapList){
                String sheetName= (String) sheetMap.get("sheetName");
                String[] headers=(String[]) sheetMap.get("headers");
                String[] ds_titles=(String[]) sheetMap.get("ds_titles");
                int[] ds_format=(int[]) sheetMap.get("ds_format");
                int[] widths=(int[]) sheetMap.get("widths");
                List<int[]> regions=(List<int[]>) sheetMap.get("regions");
                List<Map<String,Object>> data=(List<Map<String, Object>>) sheetMap.get("data");
                //创建sheet
                createSheet(wb,sheetName, headers, ds_titles,ds_format, widths,  data,regions);
            }
            
            fileName=fileName+".xls";  
            String filename = "";  
            try{  
               filename =encodeChineseDownloadFileName(request,fileName);  
            }catch(Exception e){  
                e.printStackTrace();  
            }  
//          final String userAgent = request.getHeader("USER-AGENT");  
//            if(userAgent.indexOf( "MSIE")!=-1){//IE浏览器  
//              filename = URLEncoder.encode(fileName,"UTF8");  
//            }else if(userAgent.indexOf( "Mozilla")!=-1){//google,火狐浏览器  
//              filename = new String(fileName.getBytes(), "ISO8859-1");  
//            }else{  
//              filename = URLEncoder.encode(fileName,"UTF8");//其他浏览器  
//            }  
              
            response.setHeader("Content-disposition", filename);  
            response.setContentType("application/vnd.ms-excel");    
            response.setHeader("Content-disposition", "attachment;filename="+filename);    
            response.setHeader("Pragma", "No-cache");  
            OutputStream ouputStream = response.getOutputStream();    
            wb.write(ouputStream);    
            ouputStream.flush();    
            ouputStream.close();  
            session.setAttribute("state", "open");  
              
    }  
    /** 
     *  
     * @param wb  导出的EXCEL 
     * @param sheetName  导出的SHEET名字  当前sheet数目只为1 
     * @param headers      导出的表格的表头 
     * @param ds_titles      导出的数据 map.get(key) 对应的 key 
     * @param ds_format    导出数据的样式 
     *                          1:String left;  
     *                          2:String center    
     *                          3:String right 
     *                          4 int  right 
     *                          5:float ###,###.## right  
     *                          6:number: #.00% 百分比 right 
     *                          101:date: yyyy-MM-dd left
     * @param widths      表格的列宽度  默认为 256*14 
     * @param data        数据集  List<Map> 
     * @param regions     合并单元格序号【参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列  】
     * @param response 
     * @throws IOException 
     */  
    public static void createSheet(HSSFWorkbook wb, String sheetName,String[] headers,String[] ds_titles,int[] ds_format,int[] widths, List<Map<String,Object>> data,List<int[]> regions) throws IOException {  
        if(widths==null){  
              widths = new int[ds_titles.length];  
              for(int i=0;i<ds_titles.length;i++){  
                  widths[i]=width;  
              }  
          }  
          if(ds_format==null){  
              ds_format = new int[ds_titles.length];  
              for(int i=0;i<ds_titles.length;i++){  
                  ds_format[i]=1;  
              }  
          }  
            //创建一个sheet  
            HSSFSheet  sheet = wb.createSheet(StringUtils.isNotEmpty(sheetName)?sheetName:"excel");  
            //创建表头,如果没有跳过  
            int headerrow = 0;  
            if(headers!=null){
                HSSFRow  row = sheet.createRow(headerrow);  
                //表头样式  
                HSSFCellStyle style = wb.createCellStyle();    
                HSSFFont font = wb.createFont();  
                font.setBold(true);  
                font.setFontName(excelfont);  
                font.setFontHeightInPoints((short) 11);  
                style.setFont(font);  
                style.setAlignment(HorizontalAlignment.CENTER);    
                style.setBorderBottom(BorderStyle.THIN);  
                style.setBorderLeft(BorderStyle.THIN);  
                style.setBorderRight(BorderStyle.THIN);  
                style.setBorderTop(BorderStyle.THIN);  
                 for (int i = 0; i < headers.length; i++) {
                    HSSFCell cell = row.createCell(i);    
                    cell.setCellValue(headers[i]);    
                    cell.setCellStyle(style);    
                }
                headerrow++;  
            }  
            //表格主体  解析list  
            if(data != null){  
                List styleList = new ArrayList();  
                  
                for (int i = 0; i <ds_titles.length; i++) {  //列数  
                    HSSFCellStyle style = wb.createCellStyle();    
                    HSSFFont font = wb.createFont();  
                    font.setFontName(excelfont);  
                    font.setFontHeightInPoints((short) 10);  
                    style.setFont(font);  
                    style.setBorderBottom(BorderStyle.THIN);  
                    style.setBorderLeft(BorderStyle.THIN);  
                    style.setBorderRight(BorderStyle.THIN);  
                    style.setBorderTop(BorderStyle.THIN);  
                    if(ds_format[i]==1){
                        style.setAlignment(HorizontalAlignment.LEFT);    
                    }else if(ds_format[i]==2){  
                        style.setAlignment(HorizontalAlignment.CENTER);    
                    }else if(ds_format[i]==3){  
                        style.setAlignment( HorizontalAlignment.RIGHT);   
                         //int类型  
                    }else if(ds_format[i]==4){  
                        style.setAlignment(HorizontalAlignment.RIGHT);   
                         //int类型  
                        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));     
                    }else if(ds_format[i]==5){  
                        //float类型  
                        style.setAlignment(HorizontalAlignment.RIGHT);   
                        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));     
                    }else if(ds_format[i]==6){  
                        //百分比类型  
                        style.setAlignment(HorizontalAlignment.RIGHT);   
                        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));    
                    }else if(ds_format[i]==101){  
                        //日期类型  
                        style.setAlignment(HorizontalAlignment.LEFT);   
                        short df=wb.createDataFormat().getFormat("yyyy-mm-dd hh:m:ss"); 
                        style.setDataFormat(df);
                    }  
                    styleList.add(style);  
                }  
                for (int i = 0; i < data.size() ; i++) {  //行数  
                    HSSFRow  row = sheet.createRow(headerrow);  
                    Map map = data.get(i);  
                    for (int j = 0; j <ds_titles.length; j++) {  //列数  
                         HSSFCell cell = row.createCell(j);    
                         Object o = map.get(ds_titles[j]);  
                         if(o==null||"".equals(o)){  
                             cell.setCellValue("");  
                         }else if(ds_format[j]==4){
                             //int  
                             cell.setCellValue((Long.valueOf((map.get(ds_titles[j]))+"")).longValue());   
                         }else if(ds_format[j]==5|| ds_format[j]==6){  
                             //float  
                             cell.setCellValue((Double.valueOf((map.get(ds_titles[j]))+"")).doubleValue());   
                         }else if(ds_format[j]==101){  
                             //date  
                             cell.setCellValue((Date)map.get(ds_titles[j]));   
                         }else {  
                             cell.setCellValue(map.get(ds_titles[j])+"");   
                         }  
                           
                         cell.setCellStyle((HSSFCellStyle)styleList.get(j));    
                    }  
                    headerrow++;
                }
                //合并单元格
                if(regions!=null&&!regions.isEmpty()){
                    for(int[] region:regions){
                        CellRangeAddress r = new CellRangeAddress(region[0], region[1], (short) region[2], (short) region[3]);     
                        sheet.addMergedRegion(r);   
                    }
                }
                //列宽调整
                for (int i = 0; i < headers.length; i++) {
                    if(widths[i]==-1){
                        sheet.autoSizeColumn(i);
                    }else{
                        sheet.setColumnWidth((short)i,(short)widths[i]); 
                    }  
               }
            }  
    }  
      
    /**  
     * 对文件流输出下载的中文文件名进行编码 屏蔽各种浏览器版本的差异性  
     * @throws UnsupportedEncodingException   
     */    
    public static String encodeChineseDownloadFileName(    
            HttpServletRequest request, String pFileName) throws Exception {    
            
         String filename = null;      
            String agent = request.getHeader("USER-AGENT");      
            if (null != agent){      
                if (-1 != agent.indexOf("Firefox")) {//Firefox      
                    filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8"))))+ "?=";      
                }else if (-1 != agent.indexOf("Chrome")) {//Chrome      
                    filename = new String(pFileName.getBytes(), "ISO8859-1");      
                } else {//IE7+      
                    filename = java.net.URLEncoder.encode(pFileName, "UTF-8");      
                    filename = filename.replace("+", "%20");  
                }      
            } else {      
                filename = pFileName;      
            }      
            return filename;     
    }    
}


pom.xml

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>