java HSSFWorkbook 实现Excel导出
1、添加依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <dependency> <!-- 操作File好用 可选 --> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency>
2、代码实现
import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import java.io.FileOutputStream; import java.io.OutputStream; import java.util.*; public class ExcleUtils { public static void main(String[] args) { HSSFWorkbook workbook = new HSSFWorkbook(); //创建HSSFSheet对象 HSSFSheet sheet=workbook.createSheet("sheet1"); LinkedHashMap<String, Integer> cellTitle = new LinkedHashMap<>(); cellTitle.put("序号",3000); cellTitle.put("受理编号",9000); cellTitle.put("专业",6000); cellTitle.put("档案",4000); cellTitle.put("文书",4000); cellTitle.put("检材数",4000); cellTitle.put("移交人",6000); cellTitle.put("移交时间",7000); cellTitle.put("接收人",6000); cellTitle.put("接收时间",7000); //设置标题 setTitle(workbook,sheet,cellTitle,"标题"); //设置内容 LinkedHashMap<String, Object> map = new LinkedHashMap<>(); map.put("acceptNo","JWS-M-20210901800013"); map.put("identifyItemName","DNA"); map.put("entrNum","√"); map.put("appraNum","√"); map.put("evidNum",10); map.put("transferPersonName","王某"); map.put("transferDate","2021年09月01日"); map.put("sendeePersonName","张某"); map.put("sendeepersondate","2021年09月07日"); List<Map<String, Object>> datas = new ArrayList<>(); datas.add(map); setCellValueAndNO(workbook,sheet,datas); //创建文档信息 workbook.createInformationProperties(); //将文件存到浏览器设置的下载位置 String path = "E:\\"; String filename = System.currentTimeMillis()+".xls"; try { OutputStream out = new FileOutputStream(path+filename); workbook.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 设置内容,带序号 * @param workbook * @param sheet * @param datas */ public static void setCellValueAndNO(HSSFWorkbook workbook, HSSFSheet sheet,List<Map<String,Object>> datas){ HSSFRow row = sheet.createRow(2); for (int i = 0; i < datas.size(); i++) { //设置序号 HSSFCell cell = row.createCell(0); cell.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_NORMAL,new Short("13"),false)); cell.setCellValue(i+1); int index = 1; for (String key : datas.get(i).keySet()) { HSSFCell cell1 = row.createCell(index); cell1.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_NORMAL,new Short("13"),false)); if(Objects.nonNull(datas.get(i).get(key))){ cell1.setCellValue(Objects.toString(datas.get(i).get(key))); } index++; } } } /** * 设置内容 * @param workbook * @param sheet * @param datas */ public static void setCellValueAnd(HSSFWorkbook workbook, HSSFSheet sheet,List<Map<String,Object>> datas){ HSSFRow row = sheet.createRow(2); for (int i = 0; i < datas.size(); i++) { int index = 0; for (String key : datas.get(i).keySet()) { HSSFCell cell1 = row.createCell(index); cell1.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_NORMAL,new Short("13"),false)); if(Objects.nonNull(datas.get(i).get(key))){ cell1.setCellValue(Objects.toString(datas.get(i).get(key))); } index++; } } } /** * 设置标题 * @param workbook * @param sheet * @param titleMap */ public static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, LinkedHashMap<String,Integer> titleMap,String title){ //设置单元格标题宽度 Integer titleIndex = 0; for (String key : titleMap.keySet()){ sheet.setColumnWidth(titleIndex,titleMap.get(key)); titleIndex++; } sheet.addMergedRegion(new CellRangeAddress(0,0,0,titleMap.size() - 1)); //设置标识内容,创建行的单元格,从0开始 HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); row.setHeightInPoints(35); cell.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_BOLD,new Short("13"),false)); cell.setCellValue(title); int index = 0; HSSFRow row1 = sheet.createRow(1); for (String key : titleMap.keySet()){ HSSFCell cell1 = row1.createCell(index); cell1.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_BOLD,new Short("13"),false)); cell1.setCellValue(key); index++; } } /** * fontWeight: HSSFFont.BOLDWEIGHT_BOLD 加粗 * HSSFCellStyle.VERTICAL_CENTER 单元格水平居中 * @param workbook 文档对象 * @param align 单元格对齐方式 * @param fontFimily 字体 * @param fontWeight 字体是否加粗 * @param fontSize 字体大小 * @param lineFeed 是否可以换行 * @return */ public static HSSFCellStyle setCellStyle(HSSFWorkbook workbook, short align, String fontFimily, short fontWeight, short fontSize, Boolean lineFeed){ HSSFFont font = workbook.createFont(); font.setFontName(fontFimily); //加粗 font.setBoldweight(fontWeight); font.setFontHeightInPoints(fontSize); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(align);//水平居中 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//上下居中 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 style.setWrapText(lineFeed); return style; } }
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.*;
public class ExcleUtils {
public static void main(String[] args) {
HSSFWorkbook workbook = new HSSFWorkbook();
//创建HSSFSheet对象
HSSFSheet sheet=workbook.createSheet("sheet1");
LinkedHashMap<String, Integer> cellTitle = new LinkedHashMap<>();
cellTitle.put("序号",3000);
cellTitle.put("受理编号",9000);
cellTitle.put("专业",6000);
cellTitle.put("档案",4000);
cellTitle.put("文书",4000);
cellTitle.put("检材数",4000);
cellTitle.put("移交人",6000);
cellTitle.put("移交时间",7000);
cellTitle.put("接收人",6000);
cellTitle.put("接收时间",7000);
//设置标题
setTitle(workbook,sheet,cellTitle,"标题");
//设置内容
LinkedHashMap<String, Object> map = new LinkedHashMap<>();
map.put("acceptNo","JWS-M-20210901800013");
map.put("identifyItemName","DNA");
map.put("entrNum","√");
map.put("appraNum","√");
map.put("evidNum",10);
map.put("transferPersonName","王某");
map.put("transferDate","2021年09月01日");
map.put("sendeePersonName","张某");
map.put("sendeepersondate","2021年09月07日");
List<Map<String, Object>> datas = new ArrayList<>();
datas.add(map);
setCellValueAndNO(workbook,sheet,datas);
//创建文档信息
workbook.createInformationProperties();
//将文件存到浏览器设置的下载位置
String path = "E:\\";
String filename = System.currentTimeMillis()+".xls";
try {
OutputStream out = new FileOutputStream(path+filename);
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 设置内容,带序号
* @param workbook
* @param sheet
* @param datas
*/
public static void setCellValueAndNO(HSSFWorkbook workbook, HSSFSheet sheet,List<Map<String,Object>> datas){
HSSFRow row = sheet.createRow(2);
for (int i = 0; i < datas.size(); i++) {
//设置序号
HSSFCell cell = row.createCell(0);
cell.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_NORMAL,new Short("13"),false));
cell.setCellValue(i+1);
int index = 1;
for (String key : datas.get(i).keySet()) {
HSSFCell cell1 = row.createCell(index);
cell1.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_NORMAL,new Short("13"),false));
if(Objects.nonNull(datas.get(i).get(key))){
cell1.setCellValue(Objects.toString(datas.get(i).get(key)));
}
index++;
}
}
}
/**
* 设置内容
* @param workbook
* @param sheet
* @param datas
*/
public static void setCellValueAnd(HSSFWorkbook workbook, HSSFSheet sheet,List<Map<String,Object>> datas){
HSSFRow row = sheet.createRow(2);
for (int i = 0; i < datas.size(); i++) {
int index = 0;
for (String key : datas.get(i).keySet()) {
HSSFCell cell1 = row.createCell(index);
cell1.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_NORMAL,new Short("13"),false));
if(Objects.nonNull(datas.get(i).get(key))){
cell1.setCellValue(Objects.toString(datas.get(i).get(key)));
}
index++;
}
}
}
/**
* 设置标题
* @param workbook
* @param sheet
* @param titleMap
*/
public static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, LinkedHashMap<String,Integer> titleMap,String title){
//设置单元格标题宽度
Integer titleIndex = 0;
for (String key : titleMap.keySet()){
sheet.setColumnWidth(titleIndex,titleMap.get(key));
titleIndex++;
}
sheet.addMergedRegion(new CellRangeAddress(0,0,0,titleMap.size() - 1));
//设置标识内容,创建行的单元格,从0开始
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
row.setHeightInPoints(35);
cell.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_BOLD,new Short("13"),false));
cell.setCellValue(title);
int index = 0;
HSSFRow row1 = sheet.createRow(1);
for (String key : titleMap.keySet()){
HSSFCell cell1 = row1.createCell(index);
cell1.setCellStyle(setCellStyle(workbook, HSSFCellStyle.VERTICAL_CENTER,"宋体",HSSFFont.BOLDWEIGHT_BOLD,new Short("13"),false));
cell1.setCellValue(key);
index++;
}
}
/**
* fontWeight: HSSFFont.BOLDWEIGHT_BOLD 加粗
* HSSFCellStyle.VERTICAL_CENTER 单元格水平居中
* @param workbook 文档对象
* @param align 单元格对齐方式
* @param fontFimily 字体
* @param fontWeight 字体是否加粗
* @param fontSize 字体大小
* @param lineFeed 是否可以换行
* @return
*/
public static HSSFCellStyle setCellStyle(HSSFWorkbook workbook, short align, String fontFimily, short fontWeight, short fontSize, Boolean lineFeed){
HSSFFont font = workbook.createFont();
font.setFontName(fontFimily);
//加粗
font.setBoldweight(fontWeight);
font.setFontHeightInPoints(fontSize);
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setWrapText(true);
style.setVerticalAlignment(align);//水平居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//上下居中
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setWrapText(lineFeed);
return style;
}
}