import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExcelExportHelper {
public static void main(String[] args) {
Map<String, List<String>> map = new HashMap<String, List<String>>();
export(Constant.RESULT_EXCEL_FILE, map);
}
public static void export(String fileName, Map<String, List<String>> map) {
HSSFWorkbook wb = new HSSFWorkbook();
// 生成一个sheet1
HSSFSheet sheet = wb.createSheet("sheet1");
// 生成样式对象
Map<String, CellStyle> styles = createStyles(wb);
// 生成表头
createHeader(sheet, styles);
// 生成内容
createContext(sheet, styles, map);
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
wb.write(os);
}
catch (IOException e) {
e.printStackTrace();
}
File file = new File(fileName);
byte[] content = os.toByteArray();
OutputStream fos = null;
try {
fos = new FileOutputStream(file);
fos.write(content);
os.close();
fos.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
private static void createHeader(HSSFSheet sheet,
Map<String, CellStyle> styles) {
// 为 sheet 生成第一行,用于放表头信息
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0, 0);
cell.setCellValue("序号");
cell.setCellStyle(styles.get("header"));
cell = row.createCell(1, 0);
cell.setCellValue("interface");
cell.setCellStyle(styles.get("header"));
cell = row.createCell(2, 0);
cell.setCellValue("action");
cell.setCellStyle(styles.get("header"));
}
private static void createContext(HSSFSheet sheet,
Map<String, CellStyle> styles, Map<String, List<String>> map) {
int actionInUseCount = 0;
for (Map.Entry<String, List<String>> entry : map.entrySet()) {
if (entry.getValue().size() > 0) {
// 合并单元格
CellRangeAddress region = new CellRangeAddress(
actionInUseCount + 1,
actionInUseCount + entry.getValue().size(), 1, 1);
sheet.addMergedRegion(region);
for (String actionName : entry.getValue()) {
actionInUseCount++;
// 数据每增加一行,表格就再生成一行
HSSFRow row = sheet.createRow(actionInUseCount);
// 第一个单元格放序号
HSSFCell cell = row.createCell(0, 0);
cell.setCellValue(actionInUseCount);
cell.setCellStyle(styles.get("cell"));
// 第二个单元格放 InterfaceName
cell = row.createCell(1, 0);
cell.setCellValue(entry.getKey());
cell.setCellStyle(styles.get("cell"));
// 第三个单元格放 actionName
cell = row.createCell(2, 0);
cell.setCellValue(actionName);
cell.setCellStyle(styles.get("cell"));
}
}
}
// 列宽度自适应
sheet.autoSizeColumn((short) 0);
sheet.autoSizeColumn((short) 1);
sheet.autoSizeColumn((short) 2);
}
// excel样式
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style;
// 标题
Font font = wb.createFont();
// -- 字号大小
font.setFontHeightInPoints((short) 18);
// -- 字体宽度
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
// -- 左右对齐
style.setAlignment(CellStyle.ALIGN_CENTER);
// -- 上下对齐
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// -- 背景颜色
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
// -- 填充方式
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
// -- 设置边框
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
// -- 是否换行
style.setWrapText(true);
style.setFont(font);
styles.put("title", style);
// 表头
font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setFont(font);
styles.put("header", style);
// 常规 cell
font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setFont(font);
styles.put("cell", style);
// 数字格式化
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
styles.put("double", style);
// 时间格式化
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setDataFormat(
wb.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setFont(font);
styles.put("dateTime", style);
return styles;
}
}
POI 生成 EXCEL
最新推荐文章于 2022-06-02 18:27:58 发布