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);
}
}
}
}