Java基础——POI导出Excel

来源：互联网发布：artrage中文版 mac 编辑：程序博客网时间：2024/06/10 01:05

POI导出Excel：

package cn.chinatower.bss.opdev.tenantshare;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.SocketException;
import java.text.ParseException;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import cn.chinatowercom.bss.excel.IAssetEvalExcel;

public class Download implements IAssetEvalExcel{

   /*
   * 参数说明
   * dat 下载的数据
   * wb 工作簿
   * sh sheet页对象
   * input 输入流
   * classPathResource 加载模板
   * keys 取数字段名称集合
   * colNum true显示列号，false不显示列号
   * isQfw true千分位处理 false不进行千分位处理
   * isShowAll true区域全部显示 false不显示分公司
   * startRow 数据起始行
   * excelHeaders 动态改变表头信息，说明：key('2,4') value('列A') 将第2行第4列改为列A
   * sheetsName 改变sheet名称，说明：key('0') value('sheetName') 将第一个sheet页名称改为 sheetName
   * col 隐藏多列或设置多列列宽为0
   */
   private List<Map<String, Object>> dats;
   private InputStream input = null;
   private ClassPathResource classPathResource = null;
   private String[] keys = {};
   private boolean colNum;
   private boolean isQfw = true;
   private boolean isShowAll = false;
   private int startRow;
   private Map<String,String> col = null;
   private Map<String,String> excelHeaders = null;
   private Map<String,String> sheetsName = null;
   CellStyle styleHeaderRed;
   private List<String> noQfw = initDatas();

   public void setNoQfe(String[] noQfw) {
       for(String str:noQfw){
           this.noQfw.add(str);
       }
   }

   private List<String> initDatas() {
       List<String> res = Lists.newArrayList();
       String [] keys = {"PRJ_CODE","SITEID","SITE_ID","SITENUM","SUPPLIER_CODE","MATERIAL_CODE","GOODS_NO","GT_ITEM_ZB","LT_ITEM_ZB","CATEGORY_CODE"};
       for(String str:keys){
           res.add(str);
       }
       return res;
   }
   public boolean isQfw() {
       return isQfw;
   }

   public boolean isShowAll() {
       return isShowAll;
   }

   public void setShowAll(boolean isShowAll) {
       this.isShowAll = isShowAll;
   }

   public void setQfw(boolean isQfw) {
       this.isQfw = isQfw;
   }
   public Map<String, String> getSheetsName() {
       return sheetsName;
   }

   public void setSheetsName(Map<String, String> sheetsName) {
       this.sheetsName = sheetsName;
   }
   public Download(List<Map<String, Object>> dats,ClassPathResource classPathResource,String[] keys,int startRow,boolean colNum,Map<String,String> col) {
       this.dats = dats;
       this.classPathResource = classPathResource;
       this.keys = keys;
       this.startRow = startRow;
       this.colNum = colNum;
       this.col = col;
   }

   public Download(List<Map<String, Object>> dats,ClassPathResource classPathResource,String[] keys,int startRow,boolean colNum,Map<String,String> col,Map<String,String> excelHeaders) {
       this.dats = dats;
       this.classPathResource = classPathResource;
       this.keys = keys;
       this.startRow = startRow;
       this.colNum = colNum;
       this.col = col;
       this.excelHeaders = excelHeaders;
   }

   @Override
   public void write(OutputStream output) throws ParseException {
       try {
           input = classPathResource.getInputStream();
           Workbook wb = null;
           Sheet sh = null;
           if(null != excelHeaders){
               wb = WorkbookFactory.create(input);
           } else{
               XSSFWorkbook xwb = (XSSFWorkbook) WorkbookFactory.create(input);
               SXSSFWorkbook swb = new SXSSFWorkbook(xwb,1000);
               wb = swb;
           }
           if(null != sheetsName){
               for(String key:sheetsName.keySet()){
                   String sheetName = sheetsName.get(key);
                   if(key.equals("0")){
                       wb.setSheetName(Integer.parseInt(key), sheetName);
                   }
               }
           }
           sh = wb.getSheetAt(0);
           hidCols(sh,col);
           Map<String,CellStyle> mapStyle = getStyle(wb);
           CellStyle ALIGN_RIGHT = mapStyle.get("ALIGN_RIGHT");
           CellStyle ALIGN_RIGHT_QFW_NOPOINER = mapStyle.get("ALIGN_RIGHT_QFW_NOPOINER");
           CellStyle ALIGN_RIGHT_QFW_POINER = mapStyle.get("ALIGN_RIGHT_QFW_POINER");
           CellStyle ALIGN_LEFT = mapStyle.get("ALIGN_LEFT");
           CellStyle ALIGN_RIGHT_PERSENT = mapStyle.get("ALIGN_RIGHT_PERSENT");

           styleHeaderRed = mapStyle.get("styleHeaderRed");
           if(null != excelHeaders) writeHeaders(sh,excelHeaders,mapStyle);
           int col = 1; // 列号
           String pattern = "^(-)?[0-9.]*$";
           //NumberFormat format = NumberFormat.getCurrencyInstance(Locale.CHINA);
           for (int i = 0; i < dats.size(); i++) {
               Row rowTit = sh.createRow(startRow-1+i);
               rowTit.setHeight((short)((13.5/72)*96*22));
               int count = 0;
               if(colNum){
                   Cell c = rowTit.createCell(0); //序号列
                   c.setCellValue(col++);
                   c.setCellStyle(ALIGN_LEFT);
                   count = 1;
               }
               Map<String, Object> map = dats.get(i);
               for(String k:keys){
                   String value = map.get(k)==null?" ":isShowAll?map.get(k).toString():map.get(k).toString().replace("分公司", "");
                   Cell cellTit = rowTit.createCell(count++);
                   /*
                   * noQfw:千分位但不需要进行千分位处理的字段
                   */
                   if(value.matches(pattern) && !noQfw.contains(k)){
                       try {

                            boolean isNum = value.matches("^(-?\\d+)(\\.\\d+)?$"); //判断data是否为数值型
                            boolean isInteger = value.matches("^[-\\+]?[\\d]*$");   //判断data是否为整数（小数部分是否为0）
                            if(isQfw){
                               /*BigDecimal numVal = new BigDecimal(value);
                               value = format.format(numVal).replace("￥", "");
                               value = value.replace(".00", "").replace("，", "").replace(",", "");*/
                               if(isInteger){
                                   cellTit.setCellStyle(ALIGN_RIGHT_QFW_NOPOINER);
                               }else{
                                   cellTit.setCellStyle(ALIGN_RIGHT_QFW_POINER);
                               }
                           }else{
                               cellTit.setCellStyle(ALIGN_RIGHT);
                           }
                            if(isNum){
                               /*if(!isQfw && value.length() > 11 ){ //防止以科学计数法形式展示
                                   cellTit.setCellValue(value);
                               }else{*/
                                   cellTit.setCellValue(Double.parseDouble(value));
                               //}
                            }else{
                               cellTit.setCellStyle(ALIGN_RIGHT);
                               cellTit.setCellValue(value);
                            }
                       } catch (Exception e) {
                           cellTit.setCellValue("");
                       }
                   }else{
                       if (k.equals("GT_ITEM_ZB") || k.equals("LT_ITEM_ZB")) {
                           BigDecimal bd = new BigDecimal(value);
                           bd = bd.setScale(4,BigDecimal.ROUND_HALF_UP);
                           value = bd+"";
                       }
                       if(value.indexOf("%")>-1 && value.replace("%", "").matches(pattern)){
                           double val = 0;
                           try {
                               val = Double.parseDouble(value.replaceAll("%", ""))/100;
                               cellTit.setCellValue(val);
                           } catch (Exception e) {
                               cellTit.setCellValue(" ");
                           }
                           //cellTit.setCellValue(value);
                           cellTit.setCellStyle(ALIGN_RIGHT_PERSENT);
                       }else{
                           cellTit.setCellValue(value);
                           cellTit.setCellStyle(ALIGN_LEFT);
                       }
                   }
               }
           }
           wb.write(output);
           if(null != input) input.close();
           if (output != null) output.close();
           //wb.close();
       } catch (Exception e) {
           if(e instanceof IOException || e instanceof SocketException || e instanceof OpenXML4JRuntimeException){
               System.out.println("---------------cancel download----------------");
           }else{
               e.printStackTrace();
           }
       } finally {
           IOUtils.closeQuietly(output);
           IOUtils.closeQuietly(input);
       }}

   //单元格样式设置
   public Map<String,CellStyle> getStyle(Workbook wb){
       Map<String,CellStyle> map = Maps.newHashMap();
       DataFormat dataFormat = wb.createDataFormat();

       Font font = wb.createFont();
       font.setFontHeightInPoints((short)10);
       font.setFontName("宋体");

       Font fontRed = wb.createFont();
       fontRed.setFontHeightInPoints((short)10);
       fontRed.setFontName("宋体");
       fontRed.setColor(IndexedColors.RED.getIndex());
       //单元格样式
       CellStyle ALIGN_RIGHT = wb.createCellStyle(); //右对齐
       ALIGN_RIGHT.setBorderBottom(CellStyle.BORDER_THIN);
       ALIGN_RIGHT.setBorderLeft(CellStyle.BORDER_THIN);
       ALIGN_RIGHT.setBorderRight(CellStyle.BORDER_THIN);
       ALIGN_RIGHT.setBorderTop(CellStyle.BORDER_THIN);
       ALIGN_RIGHT.setAlignment(CellStyle.ALIGN_RIGHT);
       ALIGN_RIGHT.setFont(font);   //设置字体
       map.put("ALIGN_RIGHT", ALIGN_RIGHT);   //宋体十号字

       CellStyle ALIGN_RIGHT_QFW_POINER = wb.createCellStyle(); //右对齐
       ALIGN_RIGHT_QFW_POINER.setBorderBottom(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_QFW_POINER.setBorderLeft(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_QFW_POINER.setBorderRight(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_QFW_POINER.setBorderTop(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_QFW_POINER.setAlignment(CellStyle.ALIGN_RIGHT);
       ALIGN_RIGHT_QFW_POINER.setFont(font);   //设置字体
       ALIGN_RIGHT_QFW_POINER.setDataFormat(dataFormat.getFormat("#,##0.00"));
       map.put("ALIGN_RIGHT_QFW_POINER", ALIGN_RIGHT_QFW_POINER);

       CellStyle ALIGN_RIGHT_QFW_NOPOINER = wb.createCellStyle(); //右对齐
       ALIGN_RIGHT_QFW_NOPOINER.setBorderBottom(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_QFW_NOPOINER.setBorderLeft(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_QFW_NOPOINER.setBorderRight(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_QFW_NOPOINER.setBorderTop(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_QFW_NOPOINER.setAlignment(CellStyle.ALIGN_RIGHT);
       ALIGN_RIGHT_QFW_NOPOINER.setFont(font);   //设置字体
       ALIGN_RIGHT_QFW_NOPOINER.setDataFormat(dataFormat.getFormat("#,##0"));
       map.put("ALIGN_RIGHT_QFW_NOPOINER", ALIGN_RIGHT_QFW_NOPOINER);

       CellStyle ALIGN_LEFT = wb.createCellStyle(); //左对齐
       ALIGN_LEFT.setBorderBottom(CellStyle.BORDER_THIN);
       ALIGN_LEFT.setBorderLeft(CellStyle.BORDER_THIN);
       ALIGN_LEFT.setBorderRight(CellStyle.BORDER_THIN);
       ALIGN_LEFT.setBorderTop(CellStyle.BORDER_THIN);
       ALIGN_LEFT.setAlignment(CellStyle.ALIGN_LEFT);
       ALIGN_LEFT.setFont(font);   //设置字体
       map.put("ALIGN_LEFT", ALIGN_LEFT);   //宋体十号字

       CellStyle ALIGN_RIGHT_PERSENT = wb.createCellStyle(); //右对齐百分比类型
       ALIGN_RIGHT_PERSENT.setBorderBottom(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_PERSENT.setBorderLeft(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_PERSENT.setBorderRight(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_PERSENT.setBorderTop(CellStyle.BORDER_THIN);
       ALIGN_RIGHT_PERSENT.setAlignment(CellStyle.ALIGN_RIGHT);
       ALIGN_RIGHT_PERSENT.setFont(font);   //设置字体
       ALIGN_RIGHT_PERSENT.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));    //设置百分比格式
       map.put("ALIGN_RIGHT_PERSENT", ALIGN_RIGHT_PERSENT);   //宋体十号字

       //表头样式
       CellStyle styleHeader = wb.createCellStyle(); //浅蓝色背景色
       styleHeader.setBorderBottom(CellStyle.BORDER_THIN);
       styleHeader.setBorderLeft(CellStyle.BORDER_THIN);
       styleHeader.setBorderRight(CellStyle.BORDER_THIN);
       styleHeader.setBorderTop(CellStyle.BORDER_THIN);
       styleHeader.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
       styleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND);
       styleHeader.setAlignment(CellStyle.ALIGN_CENTER);
       styleHeader.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
       styleHeader.setFont(font);   //设置字体
       map.put("styleHeader", styleHeader);   //宋体十号字

       //表头样式(红色字体)
       CellStyle styleHeaderRed = wb.createCellStyle();
       styleHeaderRed.setBorderBottom(CellStyle.BORDER_THIN);
       styleHeaderRed.setBorderLeft(CellStyle.BORDER_THIN);
       styleHeaderRed.setBorderRight(CellStyle.BORDER_THIN);
       styleHeaderRed.setBorderTop(CellStyle.BORDER_THIN);
       styleHeaderRed.setAlignment(CellStyle.ALIGN_CENTER);
       styleHeaderRed.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
       //styleHeader.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
       //styleHeaderRed.setFillPattern(CellStyle.SOLID_FOREGROUND);
       styleHeaderRed.setFont(fontRed);
       map.put("styleHeaderRed", styleHeaderRed);

       return map;
   }

   /**
   * 改变局部表头内容
   * @param sh sheet对象
   * @param excelHeaders 要改变的内容；说明：key('2,4') value('列A') 将第2行第4列改为列A
   * @param mapStyle 样式
   */
   public void writeHeaders(Sheet sh,Map<String,String> excelHeaders,Map<String,CellStyle> mapStyle){
       for(String key:excelHeaders.keySet()){
           if(key.indexOf(",")>0){
               int rowNum = Integer.parseInt(key.split(",")[0]);
               int colNum = Integer.parseInt(key.split(",")[1]);
               Row row = sh.getRow(rowNum-1);
               //System.out.println("========="+key);
               if(null==row) row = sh.createRow(rowNum-1);
               row.setHeight((short)((13.5/72)*96*22));
               Cell cell = row.getCell(colNum-1);
               if(null==cell){
                   cell = row.createCell(colNum-1);
                   cell.setCellStyle(mapStyle.get("styleHeader"));
               }
               String value = excelHeaders.get(key);
               short colorIndex = cell.getCellStyle().getFillForegroundColor();
               if(value.indexOf("style")>-1){   //改样式
                   styleHeaderRed.setFillForegroundColor(colorIndex);
                   styleHeaderRed.setFillPattern(CellStyle.SOLID_FOREGROUND);
                   cell.setCellStyle(styleHeaderRed);
               }else{ //改内容
                   cell.setCellValue(value);
               }
           }
       }
   }

   /**
   * 隐藏多列或设置多列列宽为0
   * @param sh sheet对象
   * @param Map<String,String>:开始列,Integer.parseInt(String);
   * 总列数,Integer.parseInt(String);
   */

   public void hidCols(Sheet sh,Map<String,String> col) {
       int startCol1 = Integer.parseInt(col.get("startCol1"));
       int startCol2 = Integer.parseInt(col.get("startCol2"));
       int startCol3 = Integer.parseInt(col.get("startCol3"));
       int colNum = Integer.parseInt(col.get("colNum"));
       if(startCol1>0){
           for(int i = startCol1;i<=startCol1+colNum-1;i++){
               sh.setColumnHidden(i, true);
               //sh.setColumnWidth(i, 0);
           }
       }
       if(startCol2>0){
           for(int j = startCol2;j<=startCol2+colNum-1;j++){
               sh.setColumnHidden(j, true);
               //sh.setColumnWidth(j, 0);
           }
       }
       if(startCol3>0){
           for(int k = startCol3;k<=startCol3+colNum-1;k++){
               sh.setColumnHidden(k, true);
               //sh.setColumnWidth(k, 0);
           }
       }
   }
}

阅读全文

0 0