赞
踩
- <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;
- }
- }
- }
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。