import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.io.*;
import java.util.*;
public class ExportExcelUtils {
/**
* @Title: exportExcel
* @Description: 导出Excel的方法
* @author: evan @ 2014-01-09
* @param workbook
* @param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet)
* @param sheetTitle (sheet的名称)
* @param keyMap (key 对应源数据的map的key , value 表格的标题)
* @param result (表格的数据)
* @param out (输出流)
* @throws Exception
*/
public static void exportExcel(HSSFWorkbook workbook, int sheetNum,
String sheetTitle, LinkedHashMap<String, Object> keyMap, List<Map<String,Object>> result,
OutputStream out) throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
// style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setFillForegroundColor(IndexedColors.WHITE.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());
// }
// 生成表头
int cellNum = 0;
for (Map.Entry<String, Object> map : keyMap.entrySet()) {
HSSFCell cell = row.createCell(cellNum);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(map.getValue().toString());
cell.setCellValue(text.toString());
cellNum++;
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 1;
for (Map<String,Object> maps : result) {
row = sheet.createRow(index);
int cellIndex = 0;
for (Map.Entry<String, Object> map : keyMap.entrySet()) {
HSSFCell cell = row.createCell(cellIndex);
Object obj = maps.get(map.getKey());
if (obj != null ){
cell.setCellValue(obj.toString());
}else {
cell.setCellValue("");
}
cellIndex++;
}
index++;
}
}
}
/**
*
* @param data 源数据
* @param keyMap excel的表头 源数据的key ("key","序号")
* @param maxFileSize 每个文件的最大数据量 maxSheetSize 的倍数 建议100000
* @param maxSheetSize 每个sheet的条数 建议 10000
* @param sheetName sheet名
* @param fileName 文件名 不需要后缀
* @param path 生成excel的路径
* @return boolean 成功反回 true
*/
public static boolean exportExcel(List<Map<String,Object>> data , LinkedHashMap<String, Object> keyMap, int maxFileSize, int maxSheetSize, String sheetName, String fileName, String path){
boolean result = false;
OutputStream out = null;
HSSFWorkbook workbook = null;
if (data != null && data.size() > 0){
int zongSize = data.size();
int zongFileCount = 0;
// 判断需要分几个文件
if (zongSize%maxFileSize == 0){
zongFileCount = zongSize/maxFileSize;
}else{
zongFileCount = zongSize/maxFileSize+1;
}
try {
for (int i = 0; i < zongFileCount; i++) {
int fileLimit = 0;
if ((i*maxFileSize)+maxFileSize > zongSize){
fileLimit = zongSize;
}else{
fileLimit = (i*maxFileSize)+maxFileSize;
}
int fileStart = i*maxFileSize;
File file = new File(path);
if (!file.exists()){
file.mkdirs();
}
out = new FileOutputStream(path+ File.separator+fileName+"("+(i+1)+")"+".xls");
List<Map<String,Object>> fileData = new ArrayList<Map<String,Object>>();
for (int j = fileStart; j < fileLimit; j++) {
fileData.add(data.get(j));
}
int fileDataSize = fileData.size();
int sheetCount = 0;
if (fileDataSize%maxSheetSize == 0){
sheetCount = fileDataSize/maxSheetSize;
}else{
sheetCount = fileDataSize/maxSheetSize+1;
}
workbook = new HSSFWorkbook();
for (int j = 0; j < sheetCount; j++) {
int sheetStart = j*maxSheetSize;
int sheetLimt = 0;
if (j*maxSheetSize+maxSheetSize > fileDataSize){
sheetLimt = fileDataSize;
}else{
sheetLimt = j*maxSheetSize+maxSheetSize;
}
List<Map<String,Object>> sheetData = new ArrayList<Map<String,Object>>();
for (int k = sheetStart; k < sheetLimt; k++) {
sheetData.add(fileData.get(k));
}
exportExcel(workbook, j, sheetName+(j+1), keyMap, sheetData, out);
}
workbook.write(out);
result = true;
}
} catch (FileNotFoundException e) {
result = false;
e.printStackTrace();
} catch (IOException e) {
result = false;
e.printStackTrace();
} catch (Exception e) {
result = false;
e.printStackTrace();
}finally {
try {
if (out != null) {
out.close();
}
if (workbook != null){
workbook.close();
}
} catch (IOException e) {
result = false;
e.printStackTrace();
}
}
}
return result;
}
public static void main(String[] args) {
List<Map<String,Object>> data = new ArrayList<Map<String,Object>>();
LinkedHashMap<String, Object> keyMap = new LinkedHashMap<>();
keyMap.put("key","序号");
keyMap.put("name","名字");
// 每个文件最大数据量
int maxFileSize = 100000;
// 每个sheet页最大数据量
int maxSheetSize = 10000;
String title = "测试";
for (int i = 1; i < 100100; i++) {
List rowData = new ArrayList();
Map<String,Object> map = new LinkedHashMap<String,Object>();
map.put("key","key");
map.put("name","快速开始看看书看书看书");
data.add(map);
}
exportExcel(data,keyMap,maxFileSize,maxSheetSize,title,"excel","F:\\json1\\");
}
}