/*
* 第一步：拼装表头和数据
*/
// 放多个sheet的集合
List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();
//字典项
String typeCode = Tool.getTypeCodeByCategory(belongsCategory);
List<DataDictVO> dataDicts = dataDictService.getDataDictListByTypeCode(typeCode);
List<Map<String,String>> purchaseMethodData = (List<Map<String,String>>) getDataInfoBusiness.getDataSourceList2(params, "TradeGetInfo", "category", belongsCategory, statisticalTime);
//动态表头
List<List<CellInfo>> headerTwo = new LinkedList<List<CellInfo>>();
List<CellInfo> firstTwo = new LinkedList<CellInfo>();
firstTwo.add(new CellInfo("来源系统",2,1));
if(!CollectionUtils.isEmpty(dataDicts)){
for(DataDictVO dict:dataDicts){
firstTwo.add(new CellInfo(dict.getName(),1,1));
}
}
headerTwo.add(firstTwo);
List<CellInfo> second = new LinkedList<CellInfo>();
int colNum = 1;
second.add(new CellInfo("来源系统"));
if(!CollectionUtils.isEmpty(dataDicts)){
for(int i=0;i<dataDicts.size();i++){
second.add(new CellInfo("入库"));
colNum += 1;
}
}
headerTwo.add(second);
//表数据
List<List<CellInfo>> dataTwo = new LinkedList<List<CellInfo>>();for(Map<String,String> typeData:purchaseMethodData){
List<CellInfo> row = new LinkedList<CellInfo>();
row.add(new CellInfo(typeData.get("DATA_SOURCE_CODE_NAME")));
for(DataDictVO dict:dataDicts){
String code = dict.getCode() + "_ACCESS_NUM";
row.add(new CellInfo(typeData.get(code)));
}
dataTwo.add(row);
}Map<String, Object> mapTwo = new HashMap<String, Object>();
mapTwo.put("sheetName", "按类别统计");
mapTwo.put("title", "按类别统计");
mapTwo.put("unitInfo", "");
mapTwo.put("headerLength", colNum);
mapTwo.put("header", headerTwo);
mapTwo.put("data", dataTwo);
datas.add(mapTwo);
/*
* 第二步：调用工具类
*/
/**
* 导出成Excel表格
* @param sheetName sheet名称 该类多Sheet
* @param title 表格的title。eg:政府采购
* @param unitInfo 表格单位信息。eg:金额单位:万元
* @param headerLength 表头的最大列数（按未合并前的单元格算）
* @param header 表头信息（见CellInfo）如果跨行则被合并的行不再需要说明跨行信息 即：跨行信息放在第一行出现该信息的CellInfo里。后面的每行只需要输入相同的内容即可。
* @param data 同表头
* @param out 输出流 将生成的Excel数据流传输到该输出流
* @throws IOException
*/
@SuppressWarnings("unchecked")
public static HSSFWorkbook exportExcel(List<Map<String,Object>> datas) throws IOException{
//创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//表头的样式
HSSFCellStyle titlestyle = workbook.createCellStyle();// 创建样式对象
titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
titlestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titlestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titlestyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titlestyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// titlestyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// titlestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//字体
HSSFFont titleFont = workbook.createFont(); // 创建字体对象
titleFont.setFontHeightInPoints((short) 11); // 设置字体大小
titleFont.setFontName("微软雅黑"); // 设置为黑体字
// titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体
titlestyle.setFont(titleFont);
//指定当单元格内容显示不下时自动换行
titlestyle.setWrapText(true);
//表数据的样式
HSSFCellStyle style = workbook.createCellStyle();// 创建样式对象
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//字体
HSSFFont font = workbook.createFont(); // 创建字体对象
font.setFontHeightInPoints((short) 11); // 设置字体大小
font.setFontName("微软雅黑"); // 设置为黑体字
style.setFont(font);
//指定当单元格内容显示不下时自动换行
style.setWrapText(true);
for(Map<String,Object> map:datas){
String sheetName = map.get("sheetName").toString();
String title = map.get("title").toString();
String unitInfo = map.get("unitInfo").toString();
int headerLength = (int)map.get("headerLength");
List<List<CellInfo>> header = (List<List<CellInfo>>)map.get("header");
List<List<CellInfo>> data = (List<List<CellInfo>>)map.get("data");
//创建Sheet
HSSFSheet sheet = workbook.createSheet(sheetName);
//设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth(20);
//设置第一行 （单位信息，定制）
int rowNum = 0;
if(null!=unitInfo){
sheet.addMergedRegion(new CellRangeAddress(rowNum,1,0,headerLength-1));
HSSFRow rowUnit = sheet.createRow(rowNum);
HSSFCell cellUnit = rowUnit.createCell(0);
HSSFCellStyle unitStyle = workbook.createCellStyle();// 创建样式对象
unitStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
unitStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
unitStyle.setFont(font);
cellUnit.setCellStyle(unitStyle);
cellUnit.setCellValue(unitInfo);
rowNum =1;
}
//标题
if(null!=title){
rowNum++;
HSSFRow rowHeader = sheet.createRow(rowNum);
for(int i=0;i<headerLength;i++){
HSSFCell cellHeader = rowHeader.createCell(i);
cellHeader.setCellStyle(titlestyle);
cellHeader.setCellValue(title);
}
rowNum++;
HSSFRow title2 = sheet.createRow(rowNum);
for(int i=0;i<headerLength;i++){
HSSFCell cellHeader = title2.createCell(i);
cellHeader.setCellStyle(titlestyle);
cellHeader.setCellValue(title);
}
sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum,0,headerLength-1));
} //表头
for (int i = 0; i < header.size(); i++) {
//行
rowNum ++;
HSSFRow row = sheet.createRow(rowNum);
List<CellInfo> cols = header.get(i);
//创建列
int colNum = 0;
for(int j=0;j<cols.size();j++){
HSSFCell cell = row.createCell(colNum);
cell.setCellStyle(titlestyle);
cell.setCellValue(cols.get(j).getContent());
int firstRow = rowNum;
int lastRow =rowNum;
int firstCol = colNum;
int lastCol = colNum;
boolean merge = false;
if(cols.get(j).getRowSpan()>1){
lastRow += cols.get(j).getRowSpan()-1;
merge = true;
}
//如果跨行则先创建被合并的单元格（主要是不创建的话，合并后的样式引用有问题）
if(cols.get(j).getColSpan()>1){
for(int k=0;k<cols.get(j).getColSpan()-1;k++){
colNum++;
HSSFCell tmpCell = row.createCell(colNum);
tmpCell.setCellStyle(titlestyle);
tmpCell.setCellValue(cols.get(j).getContent());
}
lastCol = colNum;
merge = true;
}
colNum++;
if(merge){
sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol));
}
}
}
//表格数据
for (int i = 0; i < data.size(); i++) {
//行
rowNum ++;
HSSFRow row = sheet.createRow(rowNum);
List<CellInfo> cols = data.get(i);
//创建列
int colNum = 0;
for(int j=0;j<cols.size();j++){
HSSFCell cell = row.createCell(colNum);
cell.setCellStyle(style);
cell.setCellValue(cols.get(j).getContent());
int firstRow = rowNum;
int lastRow =rowNum;
int firstCol = colNum;
int lastCol = colNum;
boolean merge = false;
if(cols.get(j).getRowSpan()>1){
lastRow += cols.get(j).getRowSpan()-1;
merge = true;
}
//如果跨行则先创建被合并的单元格（主要是不创建的话，合并后的样式引用有问题）
if(cols.get(j).getColSpan()>1){
for(int k=0;k<cols.get(j).getColSpan()-1;k++){
colNum++;
HSSFCell tmpCell = row.createCell(colNum);
tmpCell.setCellStyle(style);
tmpCell.setCellValue(cols.get(j).getContent());
}
lastCol = colNum;
merge = true;
}
colNum++;
if(merge){
sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol));
}
}
}
rowNum++; //页底样式
HSSFCellStyle footerStyle = workbook.createCellStyle();// 创建样式对象
footerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
footerStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
footerStyle.setFont(font); int n = headerLength/4;
int m = headerLength%4;
int start = 0;
int end = n;
HSSFRow footer = sheet.createRow(rowNum);
HSSFCell unitFooter = footer.createCell(0);
unitFooter.setCellStyle(footerStyle);
unitFooter.setCellValue("填报单位："); if(m>0){
end += 1;
}
sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,end-1)); start = end;
HSSFCell timeFooter = footer.createCell(start);
timeFooter.setCellStyle(footerStyle);
timeFooter.setCellValue("填报时间：");
if(m>1){
end += n+1;
}else{
end += n;
}
sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,end-1)); start = end;
HSSFCell personFooter = footer.createCell(start);
personFooter.setCellStyle(footerStyle);
personFooter.setCellValue("填报人：");
if(m>2){
end += n+1;
}else{
end += n;
}
sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,end-1)); start = end;
HSSFCell telFooter = footer.createCell(start);
telFooter.setCellStyle(footerStyle);
telFooter.setCellValue("联系电话：");
sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,headerLength-1));
}
return workbook;
} /**
* 解决 导出excel 文件名 为中文时乱码的问题
* @param fileName 文件名
* @return
*/
public static String toUtf8String(String fileName) {
try {
return new String(fileName.getBytes("GBK"), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return fileName;
}
/**
*
* 表格内容信息
* <p>
* 表格类每个单元格信息
* </p>
* @author qinwb
* @since jdk1.7
* 2017年3月21日
*
*/public class CellInfo { /**
* 内容:默认为空
*/
private String content = "";
/**
* 跨列:默认跨1列
*/
private int colSpan = 1;
/**
* 跨行:默认跨1行
*/
private int rowSpan = 1; public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getColSpan() {
return colSpan;
}
public void setColSpan(int colSpan) {
this.colSpan = colSpan;
}
public int getRowSpan() {
return rowSpan;
}
public void setRowSpan(int rowSpan) {
this.rowSpan = rowSpan;
}
public CellInfo() {
}
public CellInfo(String content, int rowSpan, int colSpan) {
super();
this.content = content;
this.rowSpan = rowSpan;
this.colSpan = colSpan;
}
public CellInfo(String content) {
super();
this.content = content;
}
}
