Maven 依赖:
- <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>4.1.0</version>
- </dependency>
- <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- <version>4.1.0</version>
- </dependency>
模拟的实体类:
- package com.test.Word;
- import java.io.Serializable;
- /**
- * 模拟的实体类
- * @author xieh
- *
- */
- public class Test implements Serializable{
- private int id;
- private String name;
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- @Override
- public String toString() {
- return "Test [id=" + id + ", name=" + name + "]";
- }
- }
读取和写入的 excle 工具类:
- package com.test.Word;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.FileOutputStream;
- import java.io.OutputStream;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.hssf.util.HSSFColor;
- import org.apache.poi.poifs.filesystem.POIFSFileSystem;
- import org.apache.poi.ss.usermodel.Cell;
- import org.apache.poi.ss.usermodel.CellStyle;
- import org.apache.poi.ss.usermodel.CreationHelper;
- import org.apache.poi.ss.usermodel.Font;
- 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.XSSFCell;
- import org.apache.poi.xssf.usermodel.XSSFCellStyle;
- import org.apache.poi.xssf.usermodel.XSSFDataFormat;
- import org.apache.poi.xssf.usermodel.XSSFRichTextString;
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- /**
- * excle 工具类
- *
- * @author xieh 2019/11/24 使用的 jar 包是: poi-4.1.0.jar 和 poi-ooxml-4.1.0.jar
- *
- */
- public class ExcelUtil {
- /**
- * 读取 excle 内容: 注意此方法不兼容. xlsx 文件
- * @throws Exception
- */
- @SuppressWarnings("resource")
- public static List<Map<String, String>> excelRead() throws Exception {
- // 用流的方式先读取到你想要的 Excel 的文件
- //FileInputStream fis=new FileInputStream(new File(System.getProperty("user.dir")+"/src/excel.xls"));
- FileInputStream fis = new FileInputStream(new File("D:/a.xls"));
- // 解析 Excel
- POIFSFileSystem pSystem = new POIFSFileSystem(fis);
- // 获取整个 Excel
- HSSFWorkbook hb = new HSSFWorkbook(pSystem);
- System.out.println(hb.getNumCellStyles());
- // 获取第一个表单 sheet
- HSSFSheet sheet = hb.getSheetAt(0);
- // 获取第一行
- int firstrow = sheet.getFirstRowNum();
- // 获取最后一行
- int lastrow = sheet.getLastRowNum();
- // 存取最后结果
- List<Map<String, String>> result = new ArrayList<Map<String, String>>();
- // 循环行数依次获取列数
- for (int i = firstrow; i <lastrow + 1; i++) {
- // 获取哪一行 i
- Row row = sheet.getRow(i);
- // 存放每行的键值对结果
- Map<String, String> tempMap = new HashMap<String, String>();
- if (row != null) {
- // 获取这一行的第一列
- int firstcell = row.getFirstCellNum();
- // 获取这一行的最后一列
- int lastcell = row.getLastCellNum();
- // 创建一个集合, 用处将每一行的每一列数据都存入集合中
- //List<String> list = new ArrayList<String>();
- for (int j = firstcell; j <lastcell; j++) {
- // 获取第 j 列
- Cell cell = row.getCell(j);
- if (cell != null && !("").equals(cell.toString())) {
- //System.out.print(cell + "\t");
- //list.add(cell.toString());
- tempMap.put("c" + j, cell.toString().trim());
- }
- }
- // 存放每行的结果
- result.add(tempMap);
- }// row if end
- }// for end
- fis.close();
- System.out.println(result.toString());
- return result;
- }
- /**
- * 此方法兼容. xls 和 .xlsx 格式, 建议使用
- * @param path
- * @return excle 表中的内容
- * @throws Exception
- */
- @SuppressWarnings("resource")
- public static List<Map<String, String>> excelXRead(String path) throws Exception {
- // 用流的方式先读取到你想要的 Excel 的文件
- //FileInputStream fis=new FileInputStream(new File(System.getProperty("user.dir")+"/src/excel.xls"));
- File Excel = new File(path);
- String[] split = Excel.getName().split("\\."); //. 是特殊字符, 需要转义
- Workbook wb = null;
- FileInputStream fis = null;
- // 根据文件后缀 (xls/xlsx) 进行判断
- if ( "xls".equals(split[1])){
- fis = new FileInputStream(Excel); // 文件流对象
- wb = new HSSFWorkbook(fis);
- }else if ("xlsx".equals(split[1])){
- wb = new XSSFWorkbook(Excel);
- }else {
- System.out.println("文件类型错误!");
- return new ArrayList<>();
- }
- // 开始解析
- Sheet sheet = wb.getSheetAt(0); // 读取 sheet 0
- // 获取第一行
- int firstrow = sheet.getFirstRowNum() + 1; // 第一行是列名, 所以不读
- // 获取最后一行
- int lastrow = sheet.getLastRowNum();
- // 存取最后结果
- List<Map<String, String>> result = new ArrayList<Map<String, String>>();
- // 循环行数依次获取列数
- for (int i = firstrow; i <lastrow + 1; i++) {
- // 获取哪一行 i
- Row row = sheet.getRow(i);
- // 存放每行的键值对结果
- Map<String, String> tempMap = new HashMap<String, String>();
- if (row != null) {
- // 获取这一行的第一列
- int firstcell = row.getFirstCellNum();
- // 获取这一行的最后一列
- int lastcell = row.getLastCellNum();
- // 创建一个集合, 用处将每一行的每一列数据都存入集合中
- //List<String> list = new ArrayList<String>();
- for (int j = firstcell; j <lastcell; j++) {
- // 获取第 j 列
- Cell cell = row.getCell(j);
- if (cell != null && !("").equals(cell.toString())) {
- //System.out.print(cell + "\t");
- //list.add(cell.toString());
- tempMap.put("c" + j, cell.toString().trim());
- }
- }
- // 存放每行的结果
- result.add(tempMap);
- }// row if end
- }// for end
- if(null != fis){
- fis.close();
- }
- System.out.println(result.toString());
- return result;
- }
- /**
- * 创建和写入 excle 内容
- * @param exportList 实体类 list
- * @param file 生成的文件路径
- * @throws Exception
- */
- public static void writeXls(List<Test> exportList, File file) throws Exception {
- String[] options = { "ID", "内容", "名字" };
- XSSFWorkbook book = new XSSFWorkbook();
- CreationHelper createHelper = book.getCreationHelper();
- XSSFCellStyle style = book.createCellStyle();
- XSSFCellStyle dateStyle = book.createCellStyle();
- XSSFDataFormat format = book.createDataFormat();
- style.setWrapText(true);
- dateStyle.setWrapText(true);
- XSSFSheet sheet = book.createSheet("sheet");
- sheet.setColumnWidth(3, 13000);
- sheet.setDefaultColumnWidth(20);
- XSSFRow firstRow = sheet.createRow(0);
- XSSFCell[] firstCells = new XSSFCell[3];
- CellStyle styleBlue = book.createCellStyle(); // 样式对象
- // 设置单元格的背景颜色为淡蓝色
- styleBlue.setWrapText(true);// 指定当单元格内容显示不下时自动换行
- Font font = book.createFont();
- //font.setBoldweight(Font.BOLDWEIGHT_BOLD);
- font.setFontName("宋体");
- font.setFontHeight((short) 280);
- style.setFont(font);
- dateStyle.setFont(font);
- dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
- styleBlue.setFont(font);
- for (int j = 0; j <options.length; j++) {
- firstCells[j] = firstRow.createCell(j);
- firstCells[j].setCellStyle(styleBlue);
- firstCells[j].setCellValue(new XSSFRichTextString(options[j]));
- }
- getExport(sheet, style, createHelper, exportList, dateStyle);
- if (file.exists()) {
- file.delete();
- }
- file.createNewFile();
- OutputStream os = new FileOutputStream(file);
- book.write(os);
- os.close();
- }
- /**
- * excle 实体数据写入
- * @param sheet
- * @param style
- * @param createHelper
- * @param exportList
- * @param dateStyle
- */
- private static void getExport(XSSFSheet sheet, XSSFCellStyle style, CreationHelper createHelper, List<Test> exportList,
- XSSFCellStyle dateStyle) {
- // 遍历实例类的 list 集合
- for (int i = 0; i <exportList.size(); i++) {
- // 创建行
- XSSFRow row = sheet.createRow(i + 1);
- // 实体类
- Test export = exportList.get(i);
- // 第一列
- XSSFCell hotelId = row.createCell(0);
- hotelId.setCellStyle(style);
- // 第二列
- XSSFCell hotelName = row.createCell(1);
- hotelName.setCellStyle(dateStyle);
- // 第三列
- XSSFCell chargeCount = row.createCell(2);
- chargeCount.setCellStyle(style);
- // 设置值
- hotelId.setCellValue(export.getId());
- hotelName.setCellValue("测试");
- chargeCount.setCellValue(export.getName());
- // ta.append("写入 excel 开始, 行数是" + (i + 1) + "\n");
- }// for end
- }
- /**
- * 测试
- *
- * @param args
- */
- public static void main(String[] args) {
- try {
- ExcelUtil.excelRead();
- ExcelUtil.excelXRead("D:/ 柱子. xlsx");
- ExcelUtil.excelXRead("D:/a.xls");
- List<Test> show = new ArrayList<Test>();
- Test test1 = new Test();
- test1.setId(1);
- test1.setName("xieh");
- Test test2 = new Test();
- test2.setId(2);
- test2.setName("xieh");
- show.add(test1);
- show.add(test2);
- ExcelUtil.writeXls(show, new File("D:/bbb.xls"));
- ExcelUtil.writeXls(show, new File("D:/aaa.xlsx"));
- } catch (Exception e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
效果:
来源: http://www.bubuko.com/infodetail-3303007.html