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;
public class PoiExcelUtil {
public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
List<CellRangeAddress> list = new ArrayList<>();
int sheetmergerCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetmergerCount; i++) {
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;
}
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.setContentType("application/vnd.ms-excel;charset=utf-8");
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);
List<String> headerList;
ExcelWriter 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() {
}
@Override
public 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 wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet0");
int index = 0;
int flagCreateRow = 0;
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++) {
for (int j = 0; j < 10; j++) {
rowVal = createCell(sheet, rowVal, i, j, flagCreateRow == i, String.valueOf(j + i + "*"));
flagCreateRow = i + 1;
}
}
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 wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet0");
int index = 1;
int flagCreateRow = 0;
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 ++;
}
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 cellVal = rowVal.createCell(cell);
cellVal.setCellValue(val.toString().trim());
return rowVal;
}
@Data
public 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
public static List<String> wordToExl(String path) {
if (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")) {
} else if (filePath.endsWith("docx")) {
OPCPackage opcPackage = POIXMLDocument.openPackage(filePath);
POIXMLTextExtractor extractor = new XWPFWordExtractor(opcPackage);
buffer = extractor.getText();
opcPackage.close();
} else {
}
} catch (Exception e) {
}
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 {
if (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;
}