SpringBoot-Poi-Excel
SpringBoot-Poi-Excel
不多说直接上代码
需要Maven
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.6.RELEASE</version>
</parent>
<dependencies>
<!-- 开发web 项目和启动Springboot必须添加的-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.4.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>3.0.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.1</version>
<type>pom</type>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>需要的实体类
package com.excel.pojo;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
/** * @author HuAnmin * @version 1.0 * @email 3426154361@qq.com * @date 2021/3/25-13:34 * @description 类描述.... */
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class UserExcel {
private int id;
private String name;
private String age;
private String sex;
}本地版excel(ExcelUtils)
工具类
package com.excel.utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
/** * 本地 Excel表格 增删改查操作 * 一定要注意不能有空值的出现 不然后期我们统一管理表格不好管理 如果非要使用空值 可以用null代替 * 一个Excel表格如果有多张表(Excel) 没关系直接表名就行 注意的是如果你在Excel表格里手动添加的新的表或者修改了什么东西 * 一定要关闭外部的Excel 否则java就有可能找不到表格 或者数据还是原来的数据 * 我们自动识别xlss , xls 这两种格式的文件 * 而sheet 可以理解为数据库内的 表 * @author 胡安民 */
public class ExcelUtils {
// PATH 表示 Excel文件的位置 (可以理解为数据库)
// 增删改 都需要此路径 而创建文件是单独自己指定路径
// public static String PATH = "C:\\Users\\12841\\Desktop\\tb_spec.xlsx";
// public static String PATH = "C:\\Users\\12841\\Desktop\\tb_spec.xls";
public static String PATH = "C:\\Users\\12841\\Desktop\\2020100913421411.xlsx";
/** * 查询一个数据 * * @param id 定位行 * @param cos 定位类列 注意下标从1开始 因为0是id列 * @param sheet 表名 * @return 通过行和列 获取到 指定单元格内的值 */
public static String getValue(String id, int cos, String sheet) {
List<PageData> res = ExcelPOI.vttInit(PATH, sheet);
for (int i = 0; i < res.size(); i++) {
if (res.get(i).getString(0).equals(id)) {
return res.get(i).getString(cos);
}
}
return null;
}
/** * 获取一条数据 * * @param id 表的id 列值的 * @param sheetName 表名 ( 进入表格里 下面可以看到) * @return * @throws ParseException * @throws IOException */
public static Map<String, Object> selectById(String id, String sheetName) throws ParseException, IOException {
List<PageData> list = ExcelPOI.select(PATH, sheetName);
Map<String, Object> params = new HashMap<>();
FileInputStream inp = new FileInputStream(PATH);
if (ExcelPOI.type.equals("xlsx")) {
XSSFWorkbook wb = new XSSFWorkbook(inp);
XSSFSheet sheet = wb.getSheet(sheetName);
//获得总列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
if (list != null && list.size() > 0) {
for (int i = 1; i < list.size(); i++) {
String idCol = list.get(i).getString(0);
if (id.equals(idCol)) {
for (int j = 0; j < coloumNum; j++) {
params.put(list.get(0).getString(j), list.get(i).getString(j));
}
}
}
}
return params;
}
if (ExcelPOI.type.equals("xls")) {
HSSFWorkbook wb = new HSSFWorkbook(inp);
HSSFSheet sheet = wb.getSheet(sheetName);
//获得总列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
if (list != null && list.size() > 0) {
for (int i = 1; i < list.size(); i++) {
String idCol = list.get(i).getString(0);
if (id.equals(idCol)) {
for (int j = 0; j < coloumNum; j++) {
params.put(list.get(0).getString(j), list.get(i).getString(j));
}
}
}
}
}
return params;
}
/** * 查询整个表数据 * * @param sheetName * @return * @throws ParseException * @throws IOException */
public static List<Map<String, Object>> selectList(String sheetName) throws ParseException, IOException {
List<PageData> list = ExcelPOI.select(PATH, sheetName);
FileInputStream inp = new FileInputStream(PATH);
List<Map<String, Object>> recordList = new ArrayList<>();
if (ExcelPOI.type.equals("xlsx")) {
XSSFWorkbook wb = new XSSFWorkbook(inp);
XSSFSheet sheet = wb.getSheet(sheetName);
//获得总列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
Map<String, Object> params = new HashMap<>();
if (list != null && list.size() > 0) {
for (int i = 1; i < list.size(); i++) {
params = new HashMap<>();//一定要先置空 不然就没有索引
for (int j = 0; j < coloumNum; j++) {
params.put(list.get(0).getString(j), list.get(i).getString(j));
}
recordList.add(params);
}
}
return recordList;
}
if (ExcelPOI.type.equals("xls")) {
HSSFWorkbook wb = new HSSFWorkbook(inp);
HSSFSheet sheet = wb.getSheet(sheetName);
//获得总列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
Map<String, Object> params = new HashMap<>();
if (list != null && list.size() > 0) {
for (int i = 1; i < list.size(); i++) {
params = new HashMap<>();//一定要先置空 不然就没有索引
for (int j = 0; j < coloumNum; j++) {
params.put(list.get(0).getString(j), list.get(i).getString(j));
}
recordList.add(params);
}
}
}
return recordList;
}
/** * 修改一条数据 * * @param params key 是 id 列 对应的 值 value 是 * @param sheetName 表名 ( 进入表格里 下面可以看到) * @throws IOException * @throws ParseException */
public static void update(Map<String, Object> params, String sheetName) throws IOException, ParseException {
int colNum = 0;
if (ExcelPOI.type.equals("xlsx")) {
colNum = getColNum_xslx(sheetName);
}
if (ExcelPOI.type.equals("xls")) {
colNum = getColNum_xsl(sheetName);
}
List<PageData> list = ExcelPOI.select(PATH, sheetName);
if (list != null && list.size() > 0) {
for (int i = 1; i < list.size(); i++) {
String idCol = list.get(i).getString(0);
String id = params.get("id") == null ? "" : params.get("id").toString();
if (idCol.equals(id)) {
for (int j = 0; j < colNum; j++) {
String name = list.get(0).getString(j);
String record = params.get(name) == null ? "" : params.get(name).toString();
ExcelPOI.update(i - 1, j, record, PATH, sheetName);
}
}
}
}
}
/** * 增加一条数据 (再次提醒不要有空值的出现 每一列都要添加数据 实在不想添加 可以为null 而不是空着 ) * * @param params * @param sheetName 表名 ( 进入表格里 下面可以看到) * @throws IOException * @throws ParseException */
public static void add(Map<String, Object> params, String sheetName) throws IOException, ParseException {
PageData pd = new PageData();
List<PageData> list = ExcelPOI.select(PATH, sheetName);
pd = new PageData();
int colNum = 0;
if (ExcelPOI.type.equals("xlsx")) {
colNum = getColNum_xslx(sheetName);
}
if (ExcelPOI.type.equals("xls")) {
colNum = getColNum_xsl(sheetName);
}
for (int i = 0; i < colNum; i++) {
String name = list.get(0).getString(i);
String record = params.get(name) == null ? "" : params.get(name).toString();
pd.put(i, record);
}
ExcelPOI.insert(pd, PATH, sheetName);
}
/** * 将实体类转换为Map<String, Object>的方式 增加一条数据 (再次提醒不要有空值的出现 每一列都要添加数据 实在不想添加可以为null 而不是空着 ) * * @param obj * @param sheetName 表名 ( 进入表格里 下面可以看到) * @throws IOException * @throws ParseException */
public static void add(Object obj, String sheetName) {
Map<String,Object> map=convertToMap( obj);
try {
add(map,sheetName);
} catch (IOException | ParseException e) {
e.printStackTrace();
}
}
/** * web * 将实体类转换为Map<String, Object>的方式 增加一条数据 (再次提醒不要有空值的出现 每一列都要添加数据 实在不想添加可以为null 而不是空着 ) * * @param obj * @param sheetName 表名 ( 进入表格里 下面可以看到) * @throws IOException * @throws ParseException */
public static void add(Object obj, String sheetName ,Object excel) {
Map<String,Object> map=convertToMap( obj);
try {
add(map,sheetName, excel);
} catch (IOException | ParseException e) {
e.printStackTrace();
}
}
/** * WEB * 增加一条数据 (再次提醒不要有空值的出现 每一列都要添加数据 实在不想添加 可以为null 而不是空着 ) * * @param params * @param sheetName 表名 ( 进入表格里 下面可以看到) * @throws IOException * @throws ParseException */
public static void add(Map<String, Object> params, String sheetName,Object excel) throws IOException, ParseException {
PageData pd = new PageData();
List<PageData> list = ExcelPOI.select( excel, sheetName);
pd = new PageData();
int colNum = 0;
if (excel instanceof XSSFWorkbook ) {
XSSFWorkbook workbook = (XSSFWorkbook) excel;
colNum = getColNum_xslx(sheetName,workbook);
}
if (excel instanceof HSSFWorkbook) {
HSSFWorkbook workbook = (HSSFWorkbook) excel;
colNum = getColNum_xsl(sheetName,workbook);
}
for (int i = 0; i < colNum; i++) {
String name = list.get(0).getString(i);
String record = params.get(name) == null ? "" : params.get(name).toString();
pd.put(i, record);
}
ExcelPOI.insert(pd, excel, sheetName);
}
/** * 删除一行数据 * * @param id * @param sheetName 表名 ( 进入表格里 下面可以看到) * @throws IOException * @throws ParseException */
public static void delete(String id, String sheetName) throws IOException, ParseException {
PageData pd = new PageData();
pd.put("content", "");
List<PageData> list = ExcelPOI.select(PATH, sheetName);
if (list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
String idCol = list.get(i).getString(0);
if (idCol.equals(id)) {
ExcelPOI.delete(i, PATH, sheetName);
}
}
}
}
/** * 获取列数 * * @param sheetName 表名 ( 进入表格里 下面可以看到) * @return * @throws IOException */
public static int getColNum_xslx(String sheetName) throws IOException {
FileInputStream inp = new FileInputStream(PATH);
XSSFWorkbook wb = new XSSFWorkbook(inp);
XSSFSheet sheet = wb.getSheet(sheetName);
//获得总列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
return coloumNum;
}
/** * 获取列数(WEB) * * @param sheetName 表名 ( 进入表格里 下面可以看到) * @return * @throws IOException */
public static int getColNum_xslx(String sheetName, XSSFWorkbook wb) throws IOException {
XSSFSheet sheet = wb.getSheet(sheetName);
//获得总列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
return coloumNum;
}
/** * 获取列数 * * @param sheetName 表名 ( 进入表格里 下面可以看到) * @return * @throws IOException */
public static int getColNum_xsl(String sheetName) throws IOException {
FileInputStream inp = new FileInputStream(PATH);
HSSFWorkbook wb = new HSSFWorkbook(inp);
HSSFSheet sheet = wb.getSheet(sheetName);
//获得总列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
return coloumNum;
}
/** * 获取列数(WEB) * * @param sheetName 表名 ( 进入表格里 下面可以看到) * @return * @throws IOException */
public static int getColNum_xsl(String sheetName, HSSFWorkbook wb) throws IOException {
HSSFSheet sheet = wb.getSheet(sheetName);
//获得总列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
return coloumNum;
}
/** * 获取行数 * * @param sheetName 表名 ( 进入表格里 下面可以看到) * @throws IOException */
public static int getRow(String sheetName) {
int id = 0;
if (ExcelPOI.type.equals("xlsx")) {
XSSFWorkbook workbook = ExcelPOI.getExcelByPath_xslx(PATH);
XSSFSheet sheet = workbook.getSheet(sheetName);
id = ExcelPOI.getExcelRealRow(sheet) + 1;
}
if (ExcelPOI.type.equals("xls")) {
HSSFWorkbook workbook = ExcelPOI.getExcelByPath_xsl(PATH);
HSSFSheet sheet = workbook.getSheet(sheetName);
id = ExcelPOI.getExcelRealRow(sheet) + 1;
}
return id;
}
//获取最大的 id值 (注意如果你id不是有序的 那么就不要使用)
// 原理是 获取你内容的总条数 然后取值 也就是说取的是最后一条的id
public static int getMaxId(String path, String sheets) {
List<PageData> test = ExcelPOI.vttInit(path, sheets);
PageData last = null;
if (test.size() > 1) {
last = test.get(test.size() - 1);
} else if (test.size() == 1) {
return 0;
}
return Integer.parseInt(last.getString(0));
}
//获取excel 表内 指定id 是否存在 如果存在 那么 返回 当前id 所在的条数 否则 -1
public static int getXH(String id, String sheets) {
List<PageData> res = ExcelPOI.vttInit(PATH, sheets);
for (int i = 0; i < res.size(); i++) {
if (res.get(i).getString(0).equals(id)) {
return i;
}
}
return -1;
}
/** * @param excelFile 你要创建的Excel 的存储路径和文件名 比如: C:\Users\12841\Desktop\20201009134214.xlsx * @param sheetName Excel 内表名 记得不能和其他表重复 * @param cellArray 你要创建表的列名的数组 比如: ["id","name","age","sex"] * 插入的时候也是按照这个顺序的 id列必须有 而且必须是第一个 否则此工具类的 增删改查方法 都将失败 */
public static void createExcel(String excelFile, String sheetName, String[] cellArray) {
ExcelPOI.createExcel(excelFile, sheetName, cellArray);
}
/** * 反射创建Excel 通过实体类直接生成表和字段 * @param excelFile 你要创建的Excel 的存储路径和文件名 比如: C:\Users\12841\Desktop\20201009134214.xlsx * @param obj 实体类 */
public static void createExcel(String excelFile,Class obj) {
String name = obj.getName(); //获取类名
name = name.substring(name.lastIndexOf(".")+1);
Field[] declaredFields = obj.getDeclaredFields();
String[] cellArray=new String[declaredFields.length];
for (int i = 0; i < declaredFields.length; i++) {
cellArray[i]=declaredFields[i].getName();
}
createExcel(excelFile, name, cellArray);
}
/** * 反射创建Excel 通过实体类直接生成表和字段 (Web) * @Author: HuAnmin * @email: 3426154361@qq.com * @Date: 2021/3/25 18:22 * @param: obj 实体类 * @param: suffix 文件后缀 xlsx xls * @param: response * @return: java.lang.Object * @Description: 方法功能描述.... */
public static Object createExcel(Class obj,String suffix,HttpServletResponse response) {
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
String name = obj.getName(); //获取类名
name = name.substring(name.lastIndexOf(".")+1);
Field[] declaredFields = obj.getDeclaredFields();
String[] cellArray=new String[declaredFields.length];
for (int i = 0; i < declaredFields.length; i++) {
cellArray[i]=declaredFields[i].getName();
}
name+="."+suffix;
if(name.contains("xlsx")|| name.contains("xls")){
response.setHeader("Content-Disposition", "inline; filename="+name);
return ExcelPOI.createExcel(name, cellArray);
}
return null;
}
// 反射创建Excel 通过实体类直接生成表和字段 (Web) (可以合并单元格)
public static Object createExcel(Class obj,String suffix,int firstRow, int lastRow, int firstCol, int lastCol,HttpServletResponse response) {
response.setContentType("application/ynd.ms-excel;charset=UTF-8");
String name = obj.getName(); //获取类名
name = name.substring(name.lastIndexOf(".")+1);
Field[] declaredFields = obj.getDeclaredFields();
String[] cellArray=new String[declaredFields.length];
for (int i = 0; i < declaredFields.length; i++) {
cellArray[i]=declaredFields[i].getName();
}
name+="."+suffix;
if(name.contains("xlsx")|| name.contains("xls")){
response.setHeader("Content-Disposition", "inline; filename="+name);
return ExcelPOI.createExcel(name, cellArray,firstRow,lastRow,firstCol,lastCol);
}
return null;
}
//实体类的方式创建表同时插入数据
/** * @Author: HuAnmin * @email: 3426154361@qq.com * @Date: 2021/3/25 14:42 * @param: createExcelPath 创建的文件路径 * @param: obj 需要添加的值 * @return: void * @Description: 方法功能描述.... */
public static void excelCreateAndInsert(String createExcelPath,List<Object> obj){
createExcel(createExcelPath, obj.get(0).getClass()); //根据实体类创建表
String name = obj.get(0).getClass().getName(); //获取类名
name = name.substring(name.lastIndexOf(".")+1);
for (Object o : obj) {
add(o,name );
}
}
public static Map convertToMap(Object obj) {
try {
if (obj instanceof Map) {
return (Map)obj;
}
Map<String, String> returnMap = BeanUtils.describe(obj);
returnMap.remove("class");
return returnMap;
} catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e1) {
e1.printStackTrace();
}
return new HashMap();
}
//------------------------------------------ 以下代码都是 上面工具代码的底层代码 不要轻易修改 除非你能看得懂代码
/** * poi实现excel文档操作 功能类 * * @author Ivan */
static public class ExcelPOI {
public static String type;
static {
type = suffix(ExcelUtils.PATH); //获取文件后缀 不带点
}
public static String suffix(String file) {
File str1 = new File(file);
//提取扩展名
String str2 = str1.getPath().substring(str1.getPath().lastIndexOf(".") + 1);
return str2;
}
// 当前只完成内容的模糊查询
public static List<PageData> select(String path, String sheet) throws ParseException {
List<PageData> res = vttInit(path, sheet);
List<PageData> rs = new ArrayList<>();
rs.add(res.get(0));
for (int i = 1; i < res.size(); i++) {
rs.add(res.get(i));
}
return rs;
}
// 当前只完成内容的模糊查询 WEB
public static List<PageData> select(Object excel, String sheet) throws ParseException {
List<PageData> res = vttInit(excel, sheet);
List<PageData> rs = new ArrayList<>();
rs.add(res.get(0));
for (int i = 1; i < res.size(); i++) {
rs.add(res.get(i));
}
return rs;
}
public static List<PageData> selectMore(String path, String sheet) throws ParseException {
List<PageData> res = vttInit(path, sheet);
List<PageData> rs = new ArrayList<>();
rs.add(res.get(0));
for (int i = 1; i < res.size(); i++) {
rs.add(res.get(i));
}
return rs;
}
public static void insert(PageData pd, String path, String sheets) throws IOException {
if (type.equals("xlsx")) {
XSSFWorkbook workbook = getExcelByPath_xslx(path);
XSSFSheet sheet = workbook.getSheet(sheets);
XSSFRow row = sheet.getRow(0);
int cell_end = row.getLastCellNum();// 这个就是列数
int row_end = getExcelRealRow(sheet);// 这是行数
row = sheet.createRow(row_end + 1);
for (int i = 0; i < cell_end; i++) {
row.createCell(i).setCellValue(pd.getString(i));
}
FileOutputStream out = new FileOutputStream(path);
workbook.write(out);
out.close();
return;
}
if (type.equals("xls")) {
HSSFWorkbook workbook = getExcelByPath_xsl(path);
HSSFSheet sheet = workbook.getSheet(sheets);
HSSFRow row = sheet.getRow(0);
int cell_end = row.getLastCellNum();// 这个就是列数
int row_end = getExcelRealRow(sheet);// 这是行数
row = sheet.createRow(row_end + 1);
for (int i = 0; i < cell_end; i++) {
row.createCell(i).setCellValue(pd.getString(i));
}
FileOutputStream out = new FileOutputStream(path);
workbook.write(out);
out.close();
}
}
//WEB
public static void insert(PageData pd, Object excel, String sheets) throws IOException {
if (excel instanceof XSSFWorkbook ) {
XSSFWorkbook workbook = (XSSFWorkbook)excel;
XSSFSheet sheet = workbook.getSheet(sheets);
XSSFRow row = sheet.getRow(0);
int cell_end = row.getLastCellNum();// 这个就是列数
int row_end = getExcelRealRow(sheet);// 这是行数
row = sheet.createRow(row_end + 1);
for (int i = 0; i < cell_end; i++) {
row.createCell(i).setCellValue(pd.getString(i));
}
return;
}
if (excel instanceof HSSFWorkbook ) {
HSSFWorkbook workbook =( HSSFWorkbook) excel;
HSSFSheet sheet = workbook.getSheet(sheets);
HSSFRow row = sheet.getRow(0);
int cell_end = row.getLastCellNum();// 这个就是列数
int row_end = getExcelRealRow(sheet);// 这是行数
row = sheet.createRow(row_end + 1);
for (int i = 0; i < cell_end; i++) {
row.createCell(i).setCellValue(pd.getString(i));
}
}
}
public static void delete(int rowIndex, String path, String sheets) throws IOException {
if (type.equals("xlsx")) {
XSSFWorkbook workbook = getExcelByPath_xslx(path);
XSSFSheet sheet = workbook.getSheet(sheets);
int lastRowNum = sheet.getLastRowNum();
if (rowIndex >= 0 && rowIndex < lastRowNum){
sheet.shiftRows(rowIndex + 1, lastRowNum, -1);// 将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
}
if (rowIndex == lastRowNum) {
XSSFRow removingRow = sheet.getRow(rowIndex);
if (removingRow != null){
sheet.removeRow(removingRow);
}
}
FileOutputStream out = new FileOutputStream(path);
workbook.write(out);
out.close();
return;
}
if (type.equals("xls")) {
HSSFWorkbook workbook = getExcelByPath_xsl(path);
HSSFSheet sheet = workbook.getSheet(sheets);
int lastRowNum = sheet.getLastRowNum();
if (rowIndex >= 0 && rowIndex < lastRowNum){
sheet.shiftRows(rowIndex + 1, lastRowNum, -1);// 将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
}
if (rowIndex == lastRowNum) {
HSSFRow removingRow = sheet.getRow(rowIndex);
if (removingRow != null){
sheet.removeRow(removingRow);
}
}
FileOutputStream out = new FileOutputStream(path);
workbook.write(out);
out.close();
}
}
/** * @param rowNum 行数 * @param colNum 列数 * @param value * @param path * @param sheets * @throws IOException */
public static void update(int rowNum, int colNum, String value, String path, String sheets) throws IOException {
if (type.equals("xlsx")) {
XSSFWorkbook workbook = getExcelByPath_xslx(path);
XSSFSheet sheet = workbook.getSheet(sheets);
XSSFRow row = sheet.getRow(rowNum + 1);
XSSFCell cell = row.getCell(colNum);
if (cell == null) {
row.createCell(colNum).setCellValue(value);
} else {
row.getCell(colNum).setCellValue(value);
}
FileOutputStream out = new FileOutputStream(path);
workbook.write(out);
out.close();
return;
}
if (type.equals("xls")) {
HSSFWorkbook workbook = getExcelByPath_xsl(path);
HSSFSheet sheet = workbook.getSheet(sheets);
HSSFRow row = sheet.getRow(rowNum + 1);
HSSFCell cell = row.getCell(colNum);
if (cell == null) {
row.createCell(colNum).setCellValue(value);
} else {
row.getCell(colNum).setCellValue(value);
}
FileOutputStream out = new FileOutputStream(path);
workbook.write(out);
out.close();
}
}
public static List<PageData> vttInit(String path, String sheets) {
List<PageData> rs = new ArrayList<>();
if (type.equals("xls")) {
HSSFWorkbook workbook = getExcelByPath_xsl(path);
HSSFSheet sheet = workbook.getSheet(sheets);
// 得到Excel表格
HSSFRow row = sheet.getRow(0);
// 得到Excel工作表指定行的单元格
HSSFCell cell = row.getCell(0);
int cell_end = row.getLastCellNum();// 这个就是列数(标题)
int row_end = getExcelRealRow(sheet);// 这是行数
for (int i = 0; i <= row_end; i++) {
row = sheet.getRow(i);
PageData exd = new PageData();
for (int j = 0; j <= cell_end + 1; j++) {
if (row != null && i == 0 && j == cell_end) {
exd.put(j, "");
continue;
} else if (row != null && i == 0 && j == cell_end + 1) {
exd.put(j, "");
continue;
} else if (row != null && j < cell_end){
cell = row.getCell(j);
} else if (i != 0 && j == cell_end){
continue;
}
if (cell != null){
exd.put(j, formatCell(cell));
} else {
exd.put(j, "");
}
}
exd.put("xh", i + "");
rs.add(exd);
}
return rs;
}
if (type.equals("xlsx")) {
XSSFWorkbook workbook = getExcelByPath_xslx(path);
XSSFSheet sheet = workbook.getSheet(sheets);
// 得到Excel表格
XSSFRow row = sheet.getRow(0);
// 得到Excel工作表指定行的单元格
XSSFCell cell = row.getCell(0);
int cell_end = row.getLastCellNum();// 这个就是列数(标题)
int row_end = getExcelRealRow(sheet);// 这是行数
for (int i = 0; i <= row_end; i++) {
row = sheet.getRow(i);
PageData exd = new PageData();
for (int j = 0; j <= cell_end + 1; j++) {
if (row != null && i == 0 && j == cell_end) {
exd.put(j, "");
continue;
} else if (row != null && i == 0 && j == cell_end + 1) {
exd.put(j, "");
continue;
} else if (row != null && j < cell_end){
cell = row.getCell(j);
} else if (i != 0 && j == cell_end){
continue;
}if (cell != null){
exd.put(j, formatCell(cell));
} else {
exd.put(j, "");
}
}
exd.put("xh", i + "");
rs.add(exd);
}
}
return rs;
}
//WEB
public static List<PageData> vttInit(Object excxl, String sheets) {
List<PageData> rs = new ArrayList<>();
if ( excxl instanceof HSSFWorkbook) {
HSSFWorkbook workbook = (HSSFWorkbook) excxl;
HSSFSheet sheet = workbook.getSheet(sheets);
// 得到Excel表格
HSSFRow row = sheet.getRow(0);
// 得到Excel工作表指定行的单元格
HSSFCell cell = row.getCell(0);
int cell_end = row.getLastCellNum();// 这个就是列数(标题)
int row_end = getExcelRealRow(sheet);// 这是行数
for (int i = 0; i <= row_end; i++) {
row = sheet.getRow(i);
PageData exd = new PageData();
for (int j = 0; j <= cell_end + 1; j++) {
if (row != null && i == 0 && j == cell_end) {
exd.put(j, "");
continue;
} else if (row != null && i == 0 && j == cell_end + 1) {
exd.put(j, "");
continue;
} else if (row != null && j < cell_end){
cell = row.getCell(j);
} else if (i != 0 && j == cell_end){
continue;
}
if (cell != null){
exd.put(j, formatCell(cell));
} else {
exd.put(j, "");
}
}
exd.put("xh", i + "");
rs.add(exd);
}
return rs;
}
if ( excxl instanceof XSSFWorkbook) {
XSSFWorkbook workbook = ( XSSFWorkbook) excxl;
XSSFSheet sheet = workbook.getSheet(sheets);
// 得到Excel表格
XSSFRow row = sheet.getRow(0);
// 得到Excel工作表指定行的单元格
XSSFCell cell = row.getCell(0);
int cell_end = row.getLastCellNum();// 这个就是列数(标题)
int row_end = getExcelRealRow(sheet);// 这是行数
for (int i = 0; i <= row_end; i++) {
row = sheet.getRow(i);
PageData exd = new PageData();
for (int j = 0; j <= cell_end + 1; j++) {
if (row != null && i == 0 && j == cell_end) {
exd.put(j, "");
continue;
} else if (row != null && i == 0 && j == cell_end + 1) {
exd.put(j, "");
continue;
} else if (row != null && j < cell_end){
cell = row.getCell(j);
} else if (i != 0 && j == cell_end){
continue;
}if (cell != null){
exd.put(j, formatCell(cell));
} else {
exd.put(j, "");
}
}
exd.put("xh", i + "");
rs.add(exd);
}
}
return rs;
}
// 获取Excel表的真实行数
public static int getExcelRealRow(Sheet sheet) {
boolean flag = false;
for (int i = 1; i <= sheet.getLastRowNum(); ) {
Row r = sheet.getRow(i);
if (r == null) {
// 如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
continue;
}
flag = false;
for (Cell c : r) {
if (c.getCellType() != CellType.BLANK) {
flag = true;
break;
}
}
if (flag) {
i++;
continue;
} else {
// 如果是空白行（即可能没有数据，但是有一定格式）
if (i == sheet.getLastRowNum()){// 如果到了最后一行，直接将那一行remove掉
sheet.removeRow(r);
} else {// 如果还没到最后一行，则数据往上移一行
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
}
}
}
return sheet.getLastRowNum();
}
/** * 通过文件路劲获取excel文件 (xsls格式) * * @param path * @return XSSFWorkbook */
public static XSSFWorkbook getExcelByPath_xslx(String path) {
try {
byte[] buf = IOUtils.toByteArray(new FileInputStream(path));//execelIS为InputStream流
//在需要用到InputStream的地方再封装成InputStream
ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(buf);
XSSFWorkbook workbook = new XSSFWorkbook(byteArrayInputStream);
return workbook;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/** * 通过文件路劲获取excel文件 (xslx格式) * * @param path * @return HSSFWorkbook */
public static HSSFWorkbook getExcelByPath_xsl(String path) {
try {
byte[] buf = IOUtils.toByteArray(new FileInputStream(path));//execelIS为InputStream流
//在需要用到InputStream的地方再封装成InputStream
ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(buf);
HSSFWorkbook workbook = new HSSFWorkbook(byteArrayInputStream);
return workbook;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/** * 通过文件获取excel对象 * * @param file * @return */
public HSSFWorkbook getExcelByFile(File file) {
try {
POIFSFileSystem fspoi = new POIFSFileSystem(new FileInputStream(file.getPath()));
HSSFWorkbook workbook = new HSSFWorkbook(fspoi);
return workbook;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/** * @param excelFile 你要创建的Excel 的存储路径和文件名 比如: C:\Users\12841\Desktop\20201009134214.xlsx * @param sheetName Excel 内表名 记得不能和其他表重复 * @param cellArray 你要创建表的列名的数组 比如: ["id","name","age","sex"] * 插入的时候也是按照这个顺序的 id列必须有 而且必须是第一个 否则此工具类的 增删改查方法 都将失败 */
public static void createExcel(String excelFile, String sheetName, String[] cellArray) {
if (type.equals("xlsx")) {
// 创建工作薄对象
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表对象
XSSFSheet sheet = workbook.createSheet(sheetName); // 这里也可以设置sheet的Name
// 创建工作表的行
XSSFRow row = sheet.createRow(0);// 设置第一行，从零开始
for (int i = 0; i < cellArray.length; i++) {
row.createCell(i).setCellValue(cellArray[i]);
}
try {
// 文档输出
FileOutputStream out = new FileOutputStream(excelFile);
workbook.write(out);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
return;
}
if (type.equals("xls")) {
// 创建工作薄对象
HSSFWorkbook workbook = new HSSFWorkbook();// 这里也可以设置sheet的Name
// 创建工作表对象
HSSFSheet sheet = workbook.createSheet(sheetName);
// 创建工作表的行
HSSFRow row = sheet.createRow(0);// 设置第一行，从零开始
for (int i = 0; i < cellArray.length; i++) {
row.createCell(i).setCellValue(cellArray[i]);
}
try {
// 文档输出
FileOutputStream out = new FileOutputStream(excelFile);
workbook.write(out);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static Object createExcel(String sheetName, String[] cellArray) {
type=suffix(sheetName);
String name=sheetName;
name=name.substring(0,name.lastIndexOf("."));
sheetName=name;
if (type.equals("xlsx")) {
// 创建工作薄对象
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表对象
XSSFSheet sheet = workbook.createSheet(sheetName); // 这里也可以设置sheet的Name
// 创建工作表的行
XSSFRow row = sheet.createRow(0);// 设置第一行，从零开始
for (int i = 0; i < cellArray.length; i++) {
row.createCell(i).setCellValue(cellArray[i]);
}
return workbook;
}
if (type.equals("xlsx")) {
// 创建工作薄对象
HSSFWorkbook workbook = new HSSFWorkbook();// 这里也可以设置sheet的Name
// 创建工作表对象
HSSFSheet sheet = workbook.createSheet(sheetName);
// 创建工作表的行
HSSFRow row = sheet.createRow(0);// 设置第一行，从零开始
for (int i = 0; i < cellArray.length; i++) {
row.createCell(i).setCellValue(cellArray[i]);
}
return workbook;
}
return null;
}
public static Object createExcel(String sheetName, String[] cellArray,int firstRow, int lastRow, int firstCol, int lastCol) {
type=suffix(sheetName);
String name=sheetName;
name=name.substring(0,name.lastIndexOf("."));
sheetName=name;
if (type.equals("xlsx")) {
// 创建工作薄对象
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表对象
XSSFSheet sheet = workbook.createSheet(sheetName); // 这里也可以设置sheet的Name
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol));
// 创建工作表的行
XSSFRow row = sheet.createRow(0);// 设置第一行，从零开始
for (int i = 0; i < cellArray.length; i++) {
row.createCell(i).setCellValue(cellArray[i]);
}
return workbook;
}
if (type.equals("xlsx")) {
// 创建工作薄对象
HSSFWorkbook workbook = new HSSFWorkbook();// 这里也可以设置sheet的Name
// 创建工作表对象
HSSFSheet sheet = workbook.createSheet(sheetName);
// 创建工作表的行
HSSFRow row = sheet.createRow(0);// 设置第一行，从零开始
for (int i = 0; i < cellArray.length; i++) {
row.createCell(i).setCellValue(cellArray[i]);
}
return workbook;
}
return null;
}
public static String formatCell(Cell cell) {
String ret;
switch (cell.getCellType()) {
case STRING:
ret = cell.getStringCellValue();
break;
case FORMULA:
Workbook wb = cell.getSheet().getWorkbook();
CreationHelper crateHelper = wb.getCreationHelper();
FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
ret = formatCell(evaluator.evaluateInCell(cell));
break;
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
ret = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(value);
ret = sdf.format(date);
} else {
ret = NumberToTextConverter.toText(cell.getNumericCellValue());
}
break;
case BLANK:
ret = "";
break;
case BOOLEAN:
ret = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
ret = null;
break;
default:
ret = null;
}
return ret; // 有必要自行trim
}
}
//实体类
static public class PageData extends HashMap implements Map, Serializable {
private static final long serialVersionUID = 1L;
Map map = null;
String request;
public PageData(String request) {
this.request = request;
Map properties = stringToMap(request);
Map returnMap = new HashMap();
Iterator entries = properties.entrySet().iterator();
Entry entry;
String name = "";
String value = "";
while (entries.hasNext()) {
entry = (Entry) entries.next();
name = (String) entry.getKey();
Object valueObj = entry.getValue();
if (null == valueObj) {
value = "";
} else if (valueObj instanceof String[]) {
String[] values = (String[]) valueObj;
for (int i = 0; i < values.length; i++) {
value = values[i] + ",";
}
value = value.substring(0, value.length() - 1);
} else {
value = valueObj.toString();
}
returnMap.put(name, value);
}
map = returnMap;
}
public PageData() {
map = new HashMap();
}
public PageData(ResultSet res) {
Map returnMap = new HashMap();
try {
ResultSetMetaData rsmd = res.getMetaData();
int count = rsmd.getColumnCount();
for (int i = 1; i <= count; i++) {
String key = rsmd.getColumnLabel(i);
String value = res.getString(i);
returnMap.put(key, value);
}
} catch (Exception e) {
e.printStackTrace();
}
map = returnMap;
}
public Map stringToMap(String request) {
String res[] = request.split("&");
Map resMap = new HashMap<>();
for (int i = 0; i < res.length; i++) {
String obj[] = res[i].split("=");
resMap.put(obj[0], obj[1]);
}
return resMap;
}
public String getString(Object key) {
return (String) map.get(key);
}
@SuppressWarnings("unchecked")
@Override
public Object put(Object key, Object value) {
return map.put(key, value);
}
@Override
public Object remove(Object key) {
return map.remove(key);
}
@Override
public void clear() {
map.clear();
}
@Override
public boolean containsKey(Object key) {
return map.containsKey(key);
}
@Override
public boolean containsValue(Object value) {
return map.containsValue(value);
}
@Override
public Set entrySet() {
return map.entrySet();
}
@Override
public boolean isEmpty() {
return map.isEmpty();
}
@Override
public Set keySet() {
return map.keySet();
}
@Override
@SuppressWarnings("unchecked")
public void putAll(Map t) {
map.putAll(t);
}
@Override
public int size() {
return map.size();
}
@Override
public Collection values() {
return map.values();
}
}
}测试本地版excel
package com.excel.utils;
import com.filledirection.excel.pojo.UserExcel;
import org.junit.Test;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/** * @author HuAnmin * @version 1.0 * @email 3426154361@qq.com * @date 2021/3/26-7:53 * @description 类描述.... */
public class test {
@Test
public void show111() {
String tb_spec = ExcelUtils.getValue("26", 2, "tb_spec");
System.out.println(tb_spec);
}
//获取一条对象信息 (如果有 多个id 相同那么 获取最后一个)
@Test
public void show3() throws IOException, ParseException {
//通过 id 查询 一行
Map<String, Object> map = ExcelUtils.selectById("39", "tb_spec");
System.out.println(map);
}
// 查询全部数据
@Test
public void show1() throws IOException, ParseException {
List<Map<String, Object>> tb_sku = ExcelUtils.selectList("tb_sku");
System.out.println(tb_sku);
}
//修改一条数据 必须将外部的文件关闭 否则修改失败 (另一个程序正在使用此文件，进程无法访问。)
//修改行的 每一列都要有数据 否则 默认你 将他清空 避免这种情况发生 解决办法
// 简单来说就是不修改的数据 将原数据写上 觉得的麻烦你可以先将那一列的数据先查询出来 但是不要空值 (下面演示)
@Test
public void show4() throws IOException, ParseException {
String id = "26"; //要修改数据行的 id
Map<String, Object> map = ExcelUtils.selectById(id, "tb_spec"); //查询一条数据
Map<String, Object> upmap = new HashMap<>();
upmap.put("id", id); //必须
upmap.put("name", "尺码1");
upmap.put("options", "162,175,176");
upmap.put("seq", map.get("seq"));
upmap.put("template_id", map.get("template_id"));
ExcelUtils.update(upmap, "tb_spec"); //修改数据
}
// 注意插入时候 id不要相同否则数据就不好查询了
@Test
public void show5() throws IOException, ParseException {
Map<String, Object> upmap = new HashMap<>();
//id必须 使用你自己的id生成器 或者将 id存储在Redis缓存里设置永不过期 每次调用增加 的时候获取一次 然后id+1 这样你id永远不会重复
upmap.put("id", 100);
upmap.put("name", "xxx");
upmap.put("options", "xxxx");
upmap.put("seq", "xxxx");
upmap.put("template_id", "xxxx");
ExcelUtils.add(upmap, "tb_spec");
}
//注意如果id相同默认删除第一个出现的id 所以建议在添加的时候不要id相同
//删除的原理就是将整行清空 在excel中最右边有一个搜索的小按钮 ->定位 选择空值-> 然后鼠标在选择的上方右键 -> 删除行 这样就能快速的将空行删除了
//这也是我们在上面修改和插入时候 一直强调不要有空值 的原因
//空行对我么有影响吗? 对我们后端来说是没有影响的 查询会自动过滤的 但是不影响美观
@Test
public void show6() throws IOException, ParseException {
ExcelUtils.delete("39", "tb_spec");
}
//获取列数
@Test
public void show78() throws IOException {
int tb_spec = ExcelUtils.getColNum_xsl("tb_spec");
System.out.println(tb_spec);
}
//获取行数
@Test
public void show7() {
int tb_spec = ExcelUtils.getRow("tb_spec");
System.out.println(tb_spec);
}
//获取最大的 id值 (注意如果你id不是有序的 那么就不要使用)
@Test
public void show() {
int tb_spec = ExcelUtils.getMaxId(ExcelUtils.PATH, "tb_spec");
System.out.println(tb_spec);
}
//获取excel 表内 指定id 是否存在 如果存在 那么 返回 当前id 所在的条数 否则 -1
@Test
public void show11() {
int tb_spec = ExcelUtils.getXH("38", "tb_spec");
System.out.println(tb_spec);
}
// 创建表
@Test
public void ss() {
String excelFile = "C:\\UserExcels\\12841\\Desktop\\2020100913421411.xlsx";
String sheetName = "abc";//表名
String[] cellArray = {"id", "name", "age", "sex"}; //字段
ExcelUtils.createExcel(excelFile, sheetName, cellArray);
}
// 反射的方式创建表
@Test
public void s31s() {
ExcelUtils. createExcel("C:\\UserExcels\\12841\\Desktop\\20201009134214.xlsx", UserExcel.class);
}
//反射创建表的同时插入数据
@Test
public void ss1() {
//------创建表数据
String excelFile = "C:\\UserExcels\\12841\\Desktop\\2020100913421411.xlsx";
//------插入数据
UserExcel UserExcel1= UserExcel.builder().id(1).age("22").sex("男").name("hu1").build();
UserExcel UserExcel2= UserExcel.builder().id(2).age("23").sex("女").name("hu2").build();
UserExcel UserExcel3= UserExcel.builder().id(3).age("24").sex("男").name("hu3").build();
List<Object> list=new ArrayList<Object>(){
{
add(UserExcel1);
add(UserExcel2);
add(UserExcel3);
}
};
ExcelUtils.excelCreateAndInsert(excelFile,list);
}
}web版excel(ExcelDownLoadUtils)
package com.excel.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Objects;
//网页下载版
public class ExcelDownLoadUtils {
/** * @Author: HuAnmin * @email: 3426154361@qq.com * @Date: 2021/3/26 7:52 * @param: obj 数据 (同时创建此数据类型的实体类的表) * @param: suffix 创建表的后缀 xlsx xls * @param: response * @return: void * @Description: 反射的方式创建一个excel同时插入录入数据并下载 */
public static void downloadExcel(List<Object> obj, String suffix, HttpServletResponse response) throws IOException {
Object excel = ExcelUtils.createExcel(obj.get(0).getClass(),suffix, response);
String name = obj.get(0).getClass().getName(); //获取类名
name = name.substring(name.lastIndexOf(".")+1);
if (Objects.nonNull(excel)){
if ( excel instanceof XSSFWorkbook) {
XSSFWorkbook workbook = (XSSFWorkbook) excel;
for (Object o : obj) {
ExcelUtils.add(o ,name,workbook );
}
workbook.write(response.getOutputStream());
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook .write(os);
byte[] bytes = os.toByteArray();
// 获取响应报文输出流对象
ServletOutputStream outputStream = response.getOutputStream();
// 输出
outputStream .write(bytes);
outputStream .flush();
outputStream .close();
}
if ( excel instanceof HSSFWorkbook ) {
HSSFWorkbook workbook = (HSSFWorkbook) excel;
for (Object o : obj) {
ExcelUtils.add(o ,name,workbook );
}
workbook.write(response.getOutputStream());
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook .write(os);
byte[] bytes = os.toByteArray();
// 获取响应报文输出流对象
ServletOutputStream outputStream = response.getOutputStream();
// 输出
outputStream .write(bytes);
outputStream .flush();
outputStream .close();
}
}
}
/** * 反射的方式创建一个excel同时插入录入数据并下载 (合并单元格版 ) * @param obj 数据 (同时创建此数据类型的实体类的表) * @param suffix 创建表的后缀 xlsx xls * @param firstRow 行的开始位置 (从0开始) * @param lastRow 行的结束位置 (从0开始) * @param firstCol 列的开始位置 (从0开始) * @param lastCol 列的结束位置 (从0开始) * 从无论是行还是列都是从0开始 * @param response * */
public static void downloadExcel(List<Object> obj,String suffix, int firstRow, int lastRow, int firstCol, int lastCol,HttpServletResponse response) throws IOException {
Object excel = ExcelUtils.createExcel(obj.get(0).getClass(),suffix,firstRow,lastRow,firstCol,lastCol, response);
String name = obj.get(0).getClass().getName(); //获取类名
name = name.substring(name.lastIndexOf(".")+1);
if (Objects.nonNull(excel)){
if ( excel instanceof XSSFWorkbook ) {
XSSFWorkbook workbook = (XSSFWorkbook) excel;
for (Object o : obj) {
ExcelUtils.add(o ,name,workbook );
}
workbook.write(response.getOutputStream());
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook .write(os);
byte[] bytes = os.toByteArray();
// 获取响应报文输出流对象
ServletOutputStream outputStream = response.getOutputStream();
// 输出
outputStream .write(bytes);
outputStream .flush();
outputStream .close();
}
if ( excel instanceof HSSFWorkbook) {
HSSFWorkbook workbook = (HSSFWorkbook) excel;
for (Object o : obj) {
ExcelUtils.add(o ,name,workbook );
}
workbook.write(response.getOutputStream());
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook .write(os);
byte[] bytes = os.toByteArray();
// 获取响应报文输出流对象
ServletOutputStream outputStream = response.getOutputStream();
// 输出
outputStream .write(bytes);
outputStream .flush();
outputStream .close();
}
}
}
}测试web版excel(controller)
package com.excel.controller;
import com.excel.pojo.UserExcel;
import com.excel.utils.ExcelDownLoadUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
/** * @author HuAnmin * @version 1.0 * @email 3426154361@qq.com * @date 2021/3/25-14:45 * @description 类描述.... */
@RestController
@RequestMapping("/excel")
public class ExcelController {
//导出excel
@GetMapping("/test")
public void show(HttpServletResponse response) throws IOException {
//------插入数据
UserExcel UserExcel1= UserExcel.builder().id(1).age("22").sex("男").name("hu1").build();
UserExcel UserExcel2= UserExcel.builder().id(2).age("23").sex("女").name("hu2").build();
UserExcel UserExcel3= UserExcel.builder().id(3).age("24").sex("男").name("hu3").build();
List<Object> list=new ArrayList<Object>(){
{
add(UserExcel1);
add(UserExcel2);
add(UserExcel3);
}
};
// 将list插入 自动创建list类型里的UserExcel 的excel类型是xlsx文件
//同时将list里的数据全部自动录入到UserExcel表里
ExcelDownLoadUtils.downloadExcel(list,"xlsx",response);
}
/** * 导出excel 之合并单元格 * @param response * @throws IOException */
@GetMapping("/test1")
public void show1(HttpServletResponse response) throws IOException {
try {
//------插入数据
List<Object> list=new ArrayList<Object>(){
{
add(UserExcel.builder().id(1).name("hu1").age("22").sex("男").build());
add(UserExcel.builder().id(2).name("hu2").age("23").sex("男").build());
add(UserExcel.builder().id(3).name("hu3").age("24").sex("女").build());
add(UserExcel.builder().id(4).name("hu3").age("24").sex("女").build());
add(UserExcel.builder().id(5).name("hu3").age("24").sex("男").build());
}
};
// 依据上面的数据 我们将名字重复的 合并单元格 也就是 hu3
// 将list插入 自动创建list类型里的UserExcel 的excel类型是xlsx文件
//同时将list里的数据全部自动录入到UserExcel表里
//其他参数说明：1：开始行 2：结束行 3：开始列 4：结束列 从无论是行还是列都是从0开始
//下面这行代码的意思是 将第4行到第6行和第2列到第2列的单元格合并
ExcelDownLoadUtils.downloadExcel(list,"xlsx",3,5,1,1,response);
} catch (IOException e) {
e.printStackTrace();
}
}
}启动类
package com.excel;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/** * @author HuAnmin * @version 1.0 * @email 3426154361@qq.com * @date 2021/3/25-15:17 * @description 类描述.... */
@SpringBootApplication(scanBasePackages = "com")
public class ApplictioBoot {
public static void main(String[] args) {
SpringApplication.run(ApplictioBoot.class,args);
}
}然后访问 http://localhost:8080/excel/test 或者 http://localhost:8080/excel/test1
版权说明 : 本文为转载文章, 版权归原作者所有 版权申明
原文链接 : https://blog.csdn.net/weixin_45203607/article/details/120249128
内容来源于网络，如有侵权，请联系作者删除！
