M87星云

导航

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;
}

}

posted on 2021-10-26 15:04  挽留匆匆的美丽  阅读(759)  评论(0)  编辑  收藏  举报