Java使用POI导出Excel

征尘bjajmd

已于 2023-05-08 16:23:57 修改

阅读量3.2k

点赞数 3

文章标签： java 数据库大数据

于 2023-05-08 16:06:11 首次发布

原文链接：https://blog.csdn.net/weixin_43888891/article/details/127235770

版权

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>
<dependency>
	<groupId>cn.hutool</groupId>
	<artifactId>hutool-all</artifactId>
	<version>5.8.8</version>
</dependency>

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriterTest03 {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        // 1.创建一个工作簿。03
        Workbook workbook = new HSSFWorkbook();
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("统计表");
        // 3.创建行。第一行
        Row row = sheet.createRow(0);
        // 4.创建列。
        // (1,1) 第一行第一列的单元格
        Cell cell = row.createCell(0);
        cell.setCellValue("我们都一样");
        // (1,2) 第一行第二列的单元格
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(666);

        // 第二行。(1,0)
        Row row1 = sheet.createRow(1);
        //（2,1）第二行第一列的单元格
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue(DateUtil.now());

        // 判断文件是否存在，不存在就创建
        if (FileUtil.isEmpty(new File(path))) {
            FileUtil.mkdir(path);
        }
        // 5.生成一张表。03版本的工作簿是以.xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
        // 输出
        workbook.write(fileOutputStream);
        // 6.关闭流
        fileOutputStream.close();
        System.out.println("03表生成成功！");
    }
}

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriterTest03 {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        // 1.创建一个工作簿。03
        Workbook workbook = new XSSFWorkbook(); // 07和03版本只有对象不同，其他操作一样
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("统计表");
        // 3.创建行。第一行
        Row row = sheet.createRow(0);
        // 4.创建列。
        // (1,1) 第一行第一列的单元格
        Cell cell = row.createCell(0);
        cell.setCellValue("我们都一样");
        // (1,2) 第一行第二列的单元格
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(666);

        // 第65537行。(65537,0)
        Row row1 = sheet.createRow(65536);
        //（2,1）第二行第一列的单元格
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue(DateUtil.now());

        // 判断文件是否存在，不存在就创建
        if (FileUtil.isEmpty(new File(path))) {
            FileUtil.mkdir(path);
        }
        // 5.生成一张表。03版本的工作簿是以.xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
        // 输出
        workbook.write(fileOutputStream);
        // 6.关闭流
        fileOutputStream.close();
        System.out.println("03表生成成功！");
    }
}

public class ExcelWriterTest03BigData {
    public static void main(String[] args) throws IOException {
        // 开始时间
        long start = System.currentTimeMillis();
        String path = "D:\\poi\\";
        // 1.创建一个工作簿。03
        Workbook workbook = new SXSSFWorkbook(5000);
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("统计表");
        // 3.创建行。
        for (int rowNum = 0; rowNum < 65537; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(rowNum + "," + cellNum);
            }
        }
        // 5.生成一张表。03版本的工作簿是以.xlsx结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "07BigDataUpGrade.xlsx");
        // 输出
        workbook.write(fileOutputStream);
        // 6.关闭流
        fileOutputStream.close();
        // 7.清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        System.out.println("07大数据量表优化后生成成功！");
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println("用时：" + ((end - start) / 1000) + "秒");
    }
}

@Component
public class ExcelConfig {
 
	private final static Logger logger = LoggerFactory.getLogger(ExcelConfig.class);
 
	@Value("${application.tmp.path}")
	private String applicationTmpPath;
 
	/**
	 * 设置使用SXSSFWorkbook对象导出excel报表时，TempFile使用的临时目录，代替{java.io.tmpdir}
	 */
	@PostConstruct
	public void setExcelSXSSFWorkbookTmpPath() {
		String excelSXSSFWorkbookTmpPath = applicationTmpPath + "/poifiles";
		File dir = new File(excelSXSSFWorkbookTmpPath);
		if (!dir.exists()) {
			dir.mkdirs();
		}
		TempFile.setTempFileCreationStrategy(new TempFile.DefaultTempFileCreationStrategy(dir));
		logger.info("setExcelSXSSFWorkbookTmpPath={}", excelSXSSFWorkbookTmpPath);
	}
 
}

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriterTest03BigData {
    public static void main(String[] args) throws IOException {
        // 开始时间
        long start = System.currentTimeMillis();
        String path = "D:\\poi\\";
        // 1.创建一个工作簿。03
        Workbook workbook = new HSSFWorkbook();
        // 2.创建一个工作表
        Sheet sheet = workbook.createSheet("统计表");
        // 3.创建行。
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(rowNum+","+cellNum);
            }
        }
        // 5.生成一张表。03版本的工作簿是以.xls结尾
        FileOutputStream fileOutputStream = new FileOutputStream(path + "03BigData.xls");
        // 输出
        workbook.write(fileOutputStream);
        // 6.关闭流
        fileOutputStream.close();
        System.out.println("03大数据量表生成成功！");
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println("用时："+((end-start)/1000)+"秒");
    }
}

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ClassSXSSFWorkBookUtil {

    public static void main(String[] args) throws IOException {
        long curr_time = System.currentTimeMillis();
        // 内存中缓存记录行数
        int rowAccess = 100;
        SXSSFWorkbook wb = new SXSSFWorkbook(rowAccess);
        // 生成3个SHEET
        int sheetNum = 3;

        for (int i = 0; i < sheetNum; i++) {
            Sheet sh = wb.createSheet();
            // 每一个SHEET有 200000 ROW
            for (int rowNum = 0; rowNum < 200000; rowNum++) {
                Row row = sh.createRow(rowNum);
                //每行有10个CELL
                for (int cellnum = 0; cellnum < 10; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    String address = new CellReference(cell).formatAsString();
                    cell.setCellValue(address);
                }
                // 每当行数达到设置的值就刷新数据到硬盘,以清理内存，这块本质上其实不加这个poi在达到阀值也会向临时文件写数据，
                // 假如导出60w数据3个sheet，加上手动刷新是34s，然后不加是40s，所以在一定程度上来讲手动刷新要快一点
                if (rowNum % rowAccess == 0) {
                    ((SXSSFSheet) sh).flushRows();
                }
            }
        }
        FileOutputStream os = new FileOutputStream("D:\\poi\\biggrid.xlsx");
        wb.write(os);
        os.close();
        System.out.println("耗时(秒):" + (System.currentTimeMillis() - curr_time) / 1000);
    }
}

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.IOException;

public class Test {
    public static void main(String[] args) throws IOException {
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立sheet对象
        HSSFSheet sheet = wb.createSheet("成绩表");

        // 设置列宽
        sheet.setColumnWidth(0, 25 * 256);
        sheet.setColumnWidth(1, 25 * 256);
        sheet.setColumnWidth(2, 25 * 256);
        sheet.setColumnWidth(3, 25 * 256);
        sheet.setColumnWidth(4, 25 * 256);

        // 记住一点设置单元格样式相关的都是CellStyle来控制的,设置完之后只需set给单元格即可：cell.setCellStyle(cellStyle);
        // 合并单元格后居中
        CellStyle cellStyle = wb.createCellStyle();
        // 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置字体
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);
        font.setItalic(false);
        font.setStrikeout(false);
        cellStyle.setFont(font);
        // 设置背景色
        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置边框（一般标题不设置边框，是标题下的所有表格设置边框）
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框

        //在sheet里创建第一行，参数为行索引
        HSSFRow row1 = sheet.createRow(0);
        // 合并单元格：参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

        // 创建单元格
        HSSFCell cell = row1.createCell(0);
        cell.setCellStyle(cellStyle);
        //设置单元格内容
        cell.setCellValue("学生成绩表");


        //在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        //创建单元格并设置单元格内容
        row2.createCell(0).setCellValue("姓名");
        row2.createCell(1).setCellValue("班级");
        row2.createCell(2).setCellValue("语文成绩");
        row2.createCell(3).setCellValue("数学成绩");
        row2.createCell(4).setCellValue("英语成绩");

        //在sheet里创建第三行
        HSSFRow row3 = sheet.createRow(2);
        row3.createCell(0).setCellValue("小明");
        row3.createCell(1).setCellValue("1班");
        row3.createCell(2).setCellValue(80);
        row3.createCell(3).setCellValue(75);
        row3.createCell(4).setCellValue(88);

        //在sheet里创建第四行
        HSSFRow row4 = sheet.createRow(3);
        row4.createCell(0).setCellValue("小红");
        row4.createCell(1).setCellValue("1班");
        row4.createCell(2).setCellValue(82);
        row4.createCell(3).setCellValue(70);
        row4.createCell(4).setCellValue(90);

        FileOutputStream fileOutputStream = new FileOutputStream("D:\\poi\\04.xlsx");
        wb.write(fileOutputStream);
        fileOutputStream.close();
    }
}

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;

public class ExcelReadTest03 {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        FileInputStream fileInputStream = new FileInputStream(path + "03.xlsx");
        // 1.创建一个工作簿。使得excel能操作的，这边他也能操作。
//        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Workbook workbook = new XSSFWorkbook(fileInputStream);

        // 2.得到表。
        Sheet sheet = workbook.getSheetAt(0);
        // 3.得到行。
        Row row = sheet.getRow(0);
        // 4.得到列。
        Cell cell = row.getCell(0);
        // 读取值。一定要注意类型，否则会读取失败
        System.out.println(cell.getStringCellValue());// 字符串类型
        Cell cell1 = row.getCell(1);
        System.out.println(cell1.getNumericCellValue());// 数字类型
        // 5.关闭流。
        fileInputStream.close();
    }
}

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ExcelReadTestType {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        // 1.获取文件流
        FileInputStream fileInputStream = new FileInputStream(path + "会员消费商品明细表.xls");
        // 2.创建一个工作簿。使用excel能操作的这边他也可以操作。
        Workbook workbook = new HSSFWorkbook(fileInputStream);
//        Workbook workbook = new XSSFWorkbook(fileInputStream);
        // 3.获取第一张表。
        Sheet sheet = workbook.getSheetAt(0);
        // 4.获取标题内容。
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            // 获取一行有多少列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            // 循环遍历，获取每一个标题名称
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    System.out.print(cell.getStringCellValue() + "|");
                }
            }
            System.out.println();
        }
        // 5.获取表中的记录
        // 获取有多少行记录
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            // 获取每一行记录
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                // 读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    // 获得单元格
                    Cell cell = rowData.getCell(cellNum);
                    // 匹配列的数据类型
                    String cellValueByCell = getCellValueByCell(cell);
                    System.out.println(cellValueByCell);
                }
            }
            System.out.println("----");
        }
        fileInputStream.close();
    }


    //获取单元格各类型值，返回字符串类型
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        CellType cellType = cell.getCellType();
        switch (cellType) {
            // 数字
            case NUMERIC:
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);
                        cellValue = sdf.format(date);
                    } else {
                        // 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        // 日期
                        cellValue = sdf.format(cell.getDateCellValue());
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    } finally {
                        sdf = null;
                    }
                } else {
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                    // 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                    cellValue = bd.toPlainString();
                }
                break;
            // 字符串
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            // Boolean
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue() + "";
                break;
            // 公式
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            // 空值
            case BLANK:
                cellValue = "";
                break;
            // 故障
            case ERROR:
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOW VALUE";
                break;
        }
        return cellValue;
    }
}

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;

public class GS {
    public static void main(String[] args) throws IOException {
        String path = "D:\\poi\\";
        FileInputStream fileInputStream = new FileInputStream(path + "计算公式.xls");
        // 1.创建一个工作簿。使得excel能操作的，这边他也能操作。
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        // 2.得到表。
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);
        // 拿到计算公式
        FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        // 输出单元格内容
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case FORMULA:
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);
                // 计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
    }
}