效果如下图所示
代码片
package com.service.impl;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.drools.core.rule.From;
public class ExportExcelUtils {
/**
* @Title: exportExcel
* @Description: 导出Excel的方法
* @author: evan @ 2014-01-09
* @param workbook
* @param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet)
* @param sheetTitle (sheet的名称)
* @param headers (表格的标题)
* @param result (表格的数据)
* @param out (输出流)
* @throws Exception
*/
public void exportExcel(HSSFWorkbook workbook, int sheetNum,
String sheetTitle, String[] headers, List<List> result,
OutputStream out) throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
// 设置表格默认列宽度为20个字节
// workbook.setSheetName(sheetNum, sheetTitle,
// HSSFWorkbook.ENCODING_UTF_16);
workbook.setSheetName(sheetNum, sheetTitle);
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 1;
for (List<String> m : result) {
row = sheet.createRow(index);
int cellIndex = 0;
for (String str : m) {
HSSFCell cell = row.createCell((short) cellIndex);
cell.setCellValue(str.toString());
cellIndex++;
}
index++;
}
}
}
public void exportExcel1(HSSFWorkbook workbook, int sheetNum,
String sheetTitle, String[] headers, List<Map<String, Object>> result,
OutputStream out) throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
// 设置表格默认列宽度为20个字节
// workbook.setSheetName(sheetNum, sheetTitle,
// HSSFWorkbook.ENCODING_UTF_16);
workbook.setSheetName(sheetNum, sheetTitle);
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
// 遍历集合数据,产生数据行
if (result != null) {
//循环添加数据行
int index = 1;
for (Map<String, Object> map : result) {
row = sheet.createRow(index);
//循环添加数据列
int cellIndex = 0;
//通过map.entrySet()方法
//方法一:循环map里面的每一对键值对,然后获取key和value
List<String> str1= null;
for(Entry<String, Object> vo : map.entrySet()){
vo.getKey();
vo.getValue();
str1=(List)vo.getValue();
str1.add(0, vo.getKey());
}
for (int i = 0; i < str1.size(); i++) {
HSSFCell cell = row.createCell((short) cellIndex);
cell.setCellValue(String.valueOf(str1.get(i)));
cellIndex++;
}
index++;
}
}
}
}
代码片
package com.service.impl;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import com.bean.ModelBaseReport;
import com.dao.ModelBaseReportDao;
import com.util.SheetBean;
public class PoiTest {
@SuppressWarnings("unchecked")
public static void main(String[] args) {
try {
OutputStream out = new FileOutputStream("D:\\test.xls");
List<List<String>> data = new ArrayList<List<String>>();
List<List<String>> data1 = new ArrayList<List<String>>();
List<List<String>> data2 = new ArrayList<List<String>>();
List rowData = new ArrayList();
rowData.add("0");
rowData.add("东霖柏鸿");
data.add(rowData);
data1.add(rowData);
// data2.addAll(data);
// data2.addAll(data1);
// for (int i = 0; i < data2.size(); i++) {
// List rowData = new ArrayList();
// rowData.add(String.valueOf(i));
// rowData.add(“东霖柏鸿”);
// data.add(rowData);
// }
// String[] headers = { “ID”, “用户名” };
// String[] headers1 = { “id”, “用户名” };
// String[] headers2 = { “编号”, “用户名” };
// ExportExcelUtils eeu = new ExportExcelUtils();
// HSSFWorkbook workbook = new HSSFWorkbook();
// eeu.exportExcel(workbook, 0, “上海”, headers, data1, out);
// eeu.exportExcel(workbook, 1, “深圳”, headers1, data, out);
// eeu.exportExcel(workbook, 2, “广州”, headers2, data, out);
List<ModelBaseReport> modelBaseReports = new ArrayList<ModelBaseReport>();
// clientList.add(accountNum);
// modelRateList.add(avgProb);
// modelScoreList.add(avgScore);
// modelPSIList.add(PSI);
// modelKSList.add(KS);
ModelBaseReport mReport = new ModelBaseReport();
mReport.setReportDate("20181107");
mReport.setAccountNum("100");
mReport.setAvgProb("100.01");
mReport.setAvgScore("50.47");
mReport.setPSI("0.0400");
mReport.setKS("0.0300");
ModelBaseReport mReport1 = new ModelBaseReport();
mReport1.setReportDate("20181106");
mReport1.setAccountNum("100");
mReport1.setAvgProb("100.01");
mReport1.setAvgScore("50.47");
mReport1.setPSI("0.0400");
mReport1.setKS("0.0300");
ModelBaseReport mReport2 = new ModelBaseReport();
mReport2.setReportDate("20181105");
mReport2.setAccountNum("100");
mReport2.setAvgProb("100.01");
mReport2.setAvgScore("50.47");
mReport2.setPSI("0.0400");
mReport2.setKS("0.0300");
modelBaseReports.add(0, mReport);
modelBaseReports.add(1, mReport1);
modelBaseReports.add(2, mReport2);
//模型结果list
List<Map<String, Object>> modelbaseResultList = new ArrayList<>();
//模型KS
List<Map<String, Object>> modelbaseKSList = new ArrayList<>();
//模型PSI
List<Map<String, Object>> modelbasePSIList = new ArrayList<>();
//创建字段集合
List<String> clientList = new ArrayList<>();
List<String> modelRateList = new ArrayList<>();
List<String> modelScoreList = new ArrayList<>();
List<String> modelPSIList = new ArrayList<>();
List<String> modelKSList = new ArrayList<>();
List<String> dateList = new ArrayList<>();
if (modelBaseReports != null && modelBaseReports.size() != 0) {
Map<String, Object> dateMap = new HashMap<>();
for(ModelBaseReport modelBaseReport : modelBaseReports) {
String reportDate = modelBaseReport.getReportDate();
if (!dateList.contains(reportDate)) {
dateList.add(reportDate);
}
}
Collections.sort(dateList);
dateMap.put("日期", dateList);
//modelbaseResultList.add(dateMap);
// modelbasePSIList.add(dateMap);
// modelbaseKSList.add(dateMap);
//创建map
Map<String, Object> clientMap = new HashMap<>();
Map<String, Object> modelRateMap = new HashMap<>();
Map<String, Object> modelScoreMap = new HashMap<>();
Map<String, Object> modelPSIMap = new HashMap<>();
Map<String, Object> modelKSMap = new HashMap<>();
//再循环日期集合
for(String reportDate : dateList) {
for(ModelBaseReport modelBaseReport : modelBaseReports) {
String date = modelBaseReport.getReportDate();
String accountNum = modelBaseReport.getAccountNum();
String avgProb = modelBaseReport.getAvgProb()+"%";
String avgScore = modelBaseReport.getAvgScore();
String PSI=modelBaseReport.getPSI();
String KS=modelBaseReport.getKS()+"%";
if (reportDate.equals(date)) {
clientList.add(accountNum);
modelRateList.add(avgProb);
modelScoreList.add(avgScore);
modelPSIList.add(PSI);
modelKSList.add(KS);
}
}
}
clientMap.put(“客户数”, clientList);
modelRateMap.put(“模型概率”, modelRateList);
modelScoreMap.put(“模型分数”, modelScoreList);
modelPSIMap.put(“PSI”,modelPSIList);
modelKSMap.put(“KS”,modelKSList);
modelbaseResultList.add(clientMap);
modelbaseResultList.add(modelRateMap);
modelbaseResultList.add(modelScoreMap);
modelbasePSIList.add(modelPSIMap);
modelbaseKSList.add(modelKSMap);
}
// String[] headers = { “ID”, “用户名” };
// String[] headers1 = { “id”, “用户名” };
// String[] headers2 = { “编号”, “用户名” };
ExportExcelUtils eeu = new ExportExcelUtils();
HSSFWorkbook workbook = new HSSFWorkbook();
eeu.exportExcel1(workbook, 0, “模型结果”, getTitle(dateList), modelbaseResultList, out);
eeu.exportExcel1(workbook, 1, “模型KS”, getTitle(dateList), modelbaseKSList, out);
eeu.exportExcel1(workbook, 2, “模型PSI”, getTitle(dateList), modelbasePSIList, out);
//原理就是将所有的数据一起写入,然后再关闭输入流。
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static String[] getTitle(List<String> dateList){
String[] inputVariableTitleplate = new String[dateList.size()+1];
inputVariableTitleplate[0] = "日期";
for (int i = 0; i < dateList.size(); i++) {
inputVariableTitleplate[i+1]=dateList.get(i);
}
return inputVariableTitleplate;
}
}
注:未经博主允许不得转载,谢谢。https://mp.csdn.net/mdeditor/83826314#