java导出exl

1. 依赖

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

2. 工具类

package cn.kgc.boot.util;import cn.hutool.core.io.IORuntimeException;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;/*** 功能: poi导出excel工具类**/
public class PoiExcelUtil {/*** 合并单元格处理,获取合并行** @param sheet* @return List*/public static List<CellRangeAddress> getCombineCell(Sheet sheet) {List<CellRangeAddress> list = new ArrayList<>();// 获得一个 sheet 中合并单元格的数量int sheetmergerCount = sheet.getNumMergedRegions();// 遍历所有的合并单元格for (int i = 0; i < sheetmergerCount; i++) {// 获得合并单元格保存进list中CellRangeAddress ca = sheet.getMergedRegion(i);list.add(ca);}return list;}public static int getRowNum(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) {int xr = 0;int firstC = 0;int lastC = 0;int firstR = 0;int lastR = 0;for (CellRangeAddress ca : listCombineCell) {// 获得合并单元格的起始行, 结束行, 起始列, 结束列firstC = ca.getFirstColumn();lastC = ca.getLastColumn();firstR = ca.getFirstRow();lastR = ca.getLastRow();if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {xr = lastR;}}}return xr;}/*** 判断指定的单元格是否是合并单元格** @param sheet* @param row 行下标* @param column 列下标* @return*/public static boolean isMergedRegion(Sheet sheet, int row, int column) {int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress range = sheet.getMergedRegion(i);int firstColumn = range.getFirstColumn();int lastColumn = range.getLastColumn();int firstRow = range.getFirstRow();int lastRow = range.getLastRow();if (row >= firstRow && row <= lastRow) {if (column >= firstColumn && column <= lastColumn) {return true;}}}return false;}/** 如果需要合并的话，就合并 */public static void mergeIfNeed(ExcelWriter writer,int firstRow,int lastRow,int firstColumn,int lastColumn,Object content) {if (lastRow - firstRow > 0 || lastColumn - firstColumn > 0) {writer.merge(firstRow, lastRow, firstColumn, lastColumn, content, false);} else {writer.writeCellValue(firstColumn, firstRow, content);}}public static void writeExcel(HttpServletResponse response, ExcelWriter writer) {// response为HttpServletResponse对象response.setContentType("application/vnd.ms-excel;charset=utf-8");// test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码response.setHeader("Content-Disposition", "attachment;filename=1.xls");ServletOutputStream servletOutputStream = null;try {servletOutputStream = response.getOutputStream();writer.flush(servletOutputStream);servletOutputStream.flush();} catch (IORuntimeException | IOException e) {e.printStackTrace();} finally {writer.close();try {if (servletOutputStream != null) {servletOutputStream.close();}} catch (IOException e) {e.printStackTrace();}}}public static void writeExcel(String filename, ExcelWriter writer, HttpServletResponse response) {OutputStream ouputStream = null;try {filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setHeader("Content-disposition", "attachment;filename=" + filename);ouputStream = response.getOutputStream();Runtime.getRuntime().gc();writer.flush(ouputStream);ouputStream.flush();} catch (Exception e) {e.printStackTrace();} finally {if (null != ouputStream) {try {ouputStream.close();} catch (IOException e) {e.printStackTrace();}}}}
}

3. 控制器

    @GetMapping("downExl")public void downExl(HttpServletResponse response){List<TcUser> tcUsers = new ArrayList<>();TcUser tcUser1 = new TcUser(1, "这是1", "111a");TcUser tcUser2 = new TcUser(2, "这是2", "222b");TcUser tcUser3 = new TcUser(3, "这是3", "333c");tcUsers.add(tcUser1);tcUsers.add(tcUser2);tcUsers.add(tcUser3);// 商品导出or模板List<String> headerList;// 使用 hutool创建exlExcelWriter writer = ExcelUtil.getBigWriter();String[] header = {"序号", "ID", "用户名称", "密码"};headerList = Arrays.asList(header);Sheet sheet = writer.getSheet();writer.merge(headerList.size() - 1, "测试exl");writer.writeRow(headerList);for (int i = 0; i < headerList.size(); i++) {if (i == 0 || i == 1 || i == 2 || i == 3 || i == 5 || i == 6 || i == 7 || i == 8 || i == 10 || i == 11) {sheet.setColumnWidth(i, 10 * 256);} else {sheet.setColumnWidth(i, 20 * 256);}}int row = 1;for (TcUser tcUser : tcUsers) {int firstRow = row + 1;int lastRow = row + 1;int col = -1;PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, row);PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, tcUser.getId());PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, tcUser.getUsername());PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col, tcUser.getPassword());row ++;}PoiExcelUtil.writeExcel("测试exl.xls", writer, response);}class TcUser {private Integer id;private String username,password;public TcUser() {}@Overridepublic String toString() {return "TcUser{" +"id=" + id +", username='" + username + '\'' +", password='" + password + '\'' +'}';}public TcUser(Integer id, String username, String password) {this.id = id;this.username = username;this.password = password;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}}

4. 非Http请求导出exl

	public static void exlStr() {try {String[] head = {"a", "b", "c", "d", "e", "f", "g", "h", "i", "j"};//创建HSSFWorkbook对象HSSFWorkbook wb = new HSSFWorkbook();//创建HSSFSheet对象HSSFSheet sheet = wb.createSheet("sheet0");// 表头下标 当有表头的时候调整为1 反之为0int index = 0;// 判断是否需要重新创建行int flagCreateRow = 0;// exl总行数int rowLength = 10;// 保留创建的行HSSFRow rowVal = null;// 添加表头if (index == 1){for (int i = 0; i < 1; i++) {for (int j = 0; j < head.length; j++) {rowVal = createCell(sheet, rowVal, i, j, flagCreateRow == index - 1, head[j]);flagCreateRow = i + 1;}}}// 添加数据for (int i = index; i < rowLength; i++) {//创建HSSFRow对象 创建行for (int j = 0; j < 10; j++) {rowVal = createCell(sheet, rowVal, i, j, flagCreateRow == i, String.valueOf(j + i + "*"));flagCreateRow = i + 1;}}//输出Excel文件FileOutputStream output = new FileOutputStream("C:\\Users\\HASEE\\Desktop\\aaaa\\test.xls");wb.write(output);output.flush();} catch (IOException e) {e.printStackTrace();}}public static void exlObj() {try {List<Vehicle> objs = new ArrayList<>();objs.add(new Vehicle(1, "111", 1));objs.add(new Vehicle(2, "222", 2));objs.add(new Vehicle(3, "333", 3));String[] head = {"id", "姓名", "父级"};//创建HSSFWorkbook对象HSSFWorkbook wb = new HSSFWorkbook();//创建HSSFSheet对象HSSFSheet sheet = wb.createSheet("sheet0");// 表头下标 当有表头的时候调整为1 反之为0int index = 1;// 判断是否需要重新创建行int flagCreateRow = 0;// exl总行数int rowLength = objs.size();// 保留创建的行HSSFRow rowVal = null;// 添加表头if (index == 1){for (int i = 0; i < 1; i++) {for (int j = 0; j < head.length; j++) {rowVal = createCell(sheet, rowVal, i, j, flagCreateRow == index - 1, head[j]);flagCreateRow = i + 1;}}}// 从第二行开始显示 第一行为表头int row = 1;for (Vehicle obj : objs) {int col = -1;rowVal = createCell(sheet, rowVal, row, ++col, flagCreateRow == row, obj.getVehicleID());// 这行代码必须放在这个位置flagCreateRow = row + 1;rowVal = createCell(sheet, rowVal, row, ++col, flagCreateRow == row, obj.getVehicleName());rowVal = createCell(sheet, rowVal, row, ++col, flagCreateRow == row, obj.getParentID());row ++;}//输出Excel文件FileOutputStream output = new FileOutputStream("C:\\Users\\HASEE\\Desktop\\aaaa\\test.xls");wb.write(output);output.flush();} catch (IOException e) {e.printStackTrace();}}private static HSSFRow createCell(HSSFSheet sheet, HSSFRow rowVal, int row, int cell, boolean flagCreateRow, Object val){if (flagCreateRow){rowVal = sheet.createRow(row);}//创建HSSFCell对象 创建列HSSFCell cellVal = rowVal.createCell(cell);//设置单元格的值cellVal.setCellValue(val.toString().trim());return rowVal;}@Datapublic class Vehicle {private Integer vehicleID;private String vehicleName;private Integer parentID;public Vehicle(){}public Vehicle(Integer vehicleID, String vehicleName, Integer parentID){this.parentID = parentID;this.vehicleName = vehicleName;this.vehicleID = vehicleID;}public Vehicle getThis(){return this;}}

5. 读取word

	 /*** 按java行分割符 读取*/public static List<String> wordToExl(String path) {// 获取目录下wordif (path == null) {path = "C:\\Users\\admin\\Desktop\\aaaa";}List<String> text = new ArrayList<>();for (String name : new File(path).list()) {// 文件路径String filePath = path + "\\" + name;System.out.println("--->>>>>" + filePath);String buffer = "";try {if (filePath.endsWith(".doc")) {
//                FileInputStream is = new FileInputStream(path);
//                WordExtractor ex = new WordExtractor(is);
//                buffer = ex.getText();
//                is.close();} else if (filePath.endsWith("docx")) {OPCPackage opcPackage = POIXMLDocument.openPackage(filePath);POIXMLTextExtractor extractor = new XWPFWordExtractor(opcPackage);buffer = extractor.getText();opcPackage.close();} else {}} catch (Exception e) {}
//            System.out.println(buffer);
//            System.out.println("-----------------------");
//            System.out.println();for (String s : buffer.split("\\n")) {String s1 = name.split("\\.")[0];text.add(s1 + "：" + s);}}System.out.println("--------------------------------------");System.out.println(text.toString());return text;}/*** 按照段落读取*/public static List<String> wordToExlD(String path) {List<String> text = new ArrayList<>();try {// 获取目录下wordif (path == null) {path = "C:\\Users\\admin\\Desktop\\aaaa";}for (String name : new File(path).list()) {String filePath = path + "\\" + name;InputStream is = new FileInputStream(filePath);XWPFDocument doc = new XWPFDocument(is);List<XWPFParagraph> paragraphs2 = doc.getParagraphs();for (XWPFParagraph xwpfParagraph : paragraphs2) {String str = xwpfParagraph.getParagraphText();System.out.println(str);text.add(name.split("\\.")[0] + "：" + str);}}} catch (Exception e) {}return text;}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！