一、 制作背景
女朋友在工作中使用excel时遇到了这样一个需求，想把excel每两列都合并到ab列，excel没用这个对应功能，因此我利用 java 结合poi实现了这个需求，这里记录一下，如果大家也遇到这个问题 可使用此来解决一些问题。
二、 实现效果
这里先展示一下实现后的效果
实现前的效果：
运行工具，选择该文件：
实现后的效果：
三、源码与制作exe说明
源码如下：
首先带大家看一下对应的目录层次结构：
maven坐标如下所示：
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.14</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>org.example</groupId>
<artifactId>zjy</artifactId>
<version>1.0-SNAPSHOT</version>
<name>Archetype - zjy</name>
<!--解析word文档内容-->
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.12.0</version>
</dependency>
</dependencies>
</project>
FileUploadDemo.java是使用swing开发的一个上传文件的界面，该界面我做的比较简陋（当时以能用为目的），生成的结果会默认生成到d盘下名为output.xlsx，这些你都能优化，自己懒得进行这些细节的处理：
package com.zhang;
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 javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class FileUploadDemo extends JFrame {
private JButton selectButton;
private JButton uploadButton;
public FileUploadDemo() {
setTitle("文件上传");
setDefaultCloseOperation(EXIT_ON_CLOSE);
setSize(400, 200);
setLocationRelativeTo(null);
selectButton = new JButton("请选择文件");
/* uploadButton = new JButton("上传文件");*/
JPanel panel = new JPanel();
panel.setLayout(new FlowLayout());
panel.add(selectButton);
/* panel.add(uploadButton);*/
add(panel);
selectButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
selectFile(panel);
}
});
/* uploadButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
uploadFile();
}
});*/
}
public void selectFile(JPanel panel) {
try {
JFileChooser fileChooser = new JFileChooser();
int option = fileChooser.showOpenDialog(this);
if (option == JFileChooser.APPROVE_OPTION) {
File selectedFile = fileChooser.getSelectedFile();
System.out.println("选择的文件：" + selectedFile.getAbsolutePath());
//存储填充完成后的数据
List<Map<Integer, String>> list = new ArrayList<>();
ExcelReader excelReader = new ExcelReader(selectedFile.getAbsolutePath());
List<Map<Integer, String>> rows = excelReader.getRows(0, null, null);
System.out.println(rows);
//用来存储最大列的长度
int maxKey = 0;
//1.获取最大的列值
for (int i = 0; i < rows.size(); i++) {
Map<Integer, String> map = rows.get(i);
for (Integer integer : map.keySet()) {
if (integer > maxKey) {
maxKey = integer;
}
}
}
maxKey++;
//2.填充对应数据
for (Map<Integer, String> row : rows) {
HashMap<Integer, String> hashMap = new HashMap<>();
for (int i = 0; i < maxKey; i++) {
if (row.containsKey(i)) {
hashMap.put(i, row.get(i));
} else {
hashMap.put(i, "$%#@ddfvf2sdqewerg482");
}
}
list.add(hashMap);
}
System.out.println(list);
// 写入数据
int j = 0;
int b = 1;
int k = maxKey/2;
ArrayList<arguments> arguments = new ArrayList<>();
if (maxKey%2 != 0){
k++;
}
for (int i = k; i > 0; i--) {
for (Map<Integer, String> map : list) {
String arg0 = "";
String arg1 = "";
if (j <= maxKey) {
arg0 = map.get(j);
}
if (b < maxKey) {
arg1 = map.get(b);
}
arguments arguments1 = new arguments(arg0,arg1);
arguments.add(arguments1);
}
j += 2;
b += 2;
}
for (com.zhang.arguments argument : arguments) {
System.out.println(argument);
}
//3.处理填充后的数据
// 创建一个新的工作簿
Workbook workbook = new XSSFWorkbook();
// 创建一个新的工作表
Sheet sheet = workbook.createSheet("Sheet1");
for (int i = 0; i < arguments.size(); i++) {
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
Cell cell1 = row.createCell(1);
if (arguments.get(i).getArg0().equals("$%#@ddfvf2sdqewerg482")){
cell.setCellValue("");
}else {
cell.setCellValue(arguments.get(i).getArg0());
}
if (arguments.get(i).getArg1().equals("$%#@ddfvf2sdqewerg482")){
cell1.setCellValue("");
}else {
cell1.setCellValue(arguments.get(i).getArg1());
}
}
// 将工作簿保存到文件中
try (FileOutputStream outputStream = new FileOutputStream("D://output.xlsx")) {
workbook.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, "导入失败！！！");
}
// TODO: 实现文件选择逻辑
}
public void uploadFile() {
// TODO: 实现文件上传逻辑
JFileChooser fileChooser = new JFileChooser();
int option = fileChooser.showOpenDialog(this);
if (option == JFileChooser.APPROVE_OPTION) {
File selectedFile = fileChooser.getSelectedFile();
System.out.println(selectedFile.length());
}
}
public static void main(String[] args) {
SwingUtilities.invokeLater(new Runnable() {
@Override
public void run() {
new FileUploadDemo().setVisible(true);
}
});
}
}
ExcelReader.java是主要的业务读取，处理模块：
package com.zhang;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.TimeUnit;
/**
* Excel读取类
*/
public class ExcelReader {
private String filePath; // 读取Excel文件的路径
private Workbook workbook = null; // Excel文件工作簿
private static FormulaEvaluator formula = null; // 公式计算器
private String password = ""; //excel表格密码
private Map<Integer, List<Map<Integer, String>>> setList = new HashMap<>(); //存储数据集合
//创建解析类时,若读取文件有问题,会报异常。调取类需要收集异常，若异常信息包含关键字"密码"，则需要尝试输入密码后读取。
public ExcelReader(String filePath, String password) {
this.filePath = filePath;
this.password = password;
initializeWorkbook();
}
public ExcelReader(String filePath) {
this.filePath = filePath;
initializeWorkbook();
}
/**
* 根据文件类型,初始化workbook取值
*/
private void initializeWorkbook() {
File file = new File(filePath);
try {
if (StringUtils.isBlank(password)) {
workbook = WorkbookFactory.create(file);
} else {
InputStream inp = new FileInputStream(file);
POIFSFileSystem pfs = new POIFSFileSystem(inp);
inp.close();
EncryptionInfo encInfo = new EncryptionInfo(pfs);
Decryptor decryptor = Decryptor.getInstance(encInfo);
decryptor.verifyPassword(password);
workbook = new XSSFWorkbook(decryptor.getDataStream(pfs));
}
formula = workbook.getCreationHelper().createFormulaEvaluator();
} catch (Exception e) {
//如果输入的密码错误，继续输入后接着判断
/*
先不写
*/
e.printStackTrace();
}
}
/**
* 获取excel里的所有内容
* 注意：该方法根据自己理解的.net的意思独立写的，因为Java中没有ExcelDataReader，只能用poi操作
* 类似与.net中的 DataTable
*/
public Map<Integer, List<Map<Integer, String>>> getAllSheet(){
Map<Integer, List<Map<Integer, String>>> map = new HashMap<>();
if (workbook != null) {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Boolean exist = isExist(i);
if (exist){
List<Map<Integer, String>> rows = getRows(i, null, null);
map.put(i,rows);
}
}
return map;
}
return null;
}
/**
* 判断当前excel表格内是否存在数据
*/
private Boolean isExist(int i){
for (int j = 0; j < workbook.getSheetAt(i).getLastRowNum() + 1; j++) {
if (workbook.getSheetAt(i).getRow(i) != null) {
return true;
}
}
return false;
}
/**
* 关闭Workbook
*/
public void close() {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
throw new RuntimeException("关闭WoorkBook异常");
}
}
}
/**
* 获取Excel文件中所有的sheet表名称
*/
public String[] SheetNames() {
String[] sheetNames = null;
if (workbook != null) {
//获取工作表个数
int numberOfSheets = workbook.getNumberOfSheets();
//创建数组存储sheet名称
sheetNames = new String[numberOfSheets];
for (int i = 0; i < numberOfSheets; i++) {
sheetNames[i] = workbook.getSheetName(i);
}
}
if (sheetNames == null) {
try {
throw new Exception("请确保数据上传成功");
} catch (Exception e) {
throw new RuntimeException(e);
}
}
return sheetNames;
}
/**
* 获取指定Sheet表中的所有记录.所有单元格取值都转化为字符串格式
*
* @param sheetIndex 获取sheet表的Index
* @param isTrim 是否去前面空格
* @param isReread 是否重新读取
* @return
*/
public List<Map<Integer, String>> getRows(int sheetIndex, Boolean isTrim, Boolean isReread) {
//isTrim默认为true,isReread默认为false
if (isTrim == null) {
isTrim = true;
}
if (isReread == null) {
isReread = false;
}
if (!isReread) {
if (setList != null && setList.containsKey(sheetIndex)) {
return setList.get(sheetIndex);
}
}
if (setList.containsKey(sheetIndex)) {
setList.remove(sheetIndex);
}
List<Map<Integer, String>> rows = new ArrayList<>();
if (workbook != null && workbook.getNumberOfSheets() > sheetIndex) {
for (int i = 0; i < workbook.getSheetAt(sheetIndex).getLastRowNum() + 1; i++) {
Row row = workbook.getSheetAt(sheetIndex).getRow(i);
if (row == null) {
continue;
}
Map<Integer, String> cellDict = new HashMap<>();
for (int j = 0; j < row.getLastCellNum(); j++) {
//获取单元格的值
Cell cell = row.getCell(j);
String value = getCellValue(cell);
if (value != null && value.length() > 0) {
if (isTrim) {
cellDict.put(j, value.trim());
} else {
cellDict.put(j, value);
}
}
}
rows.add(cellDict);
}
//这个地方.net是有只读集合等概念，而Java没有这个概念先这样写
//List<Dictionary<int, string>> resultList = new List<Dictionary<int, string>>();
//rows.ForEach(i => resultList.Add(new Dictionary<int, string>(i)));
setList.put(sheetIndex, rows);
return setList.get(sheetIndex);
}
return null;
}
/**
* 获取指定Sheet表中的所有记录
*
* @param sheetName 表名
* @param isTrim 是否去除前面空格
* @param isReread 是否重新获取
* @return
*/
public List<Map<Integer, String>> getRows(String sheetName, Boolean isTrim, Boolean isReread) {
//isTrim默认为true,isReread默认为false
if (isTrim == null) {
isTrim = true;
}
if (isReread == null) {
isReread = false;
}
int x = Arrays.asList(SheetNames()).indexOf(sheetName);
if (x != -1) {
return getRows(x, isTrim, isReread);
}
return null;
}
/**
* 获取指定Sheet表中的指定行的记录.所有单元格取值都转化为字符串格式
*
* @param sheetIndex 获取sheet表的Index
* @param rowIndex 行号
* @param isTrim 是否去除空格
* @return
*/
public Map<Integer, String> getRows(int sheetIndex, int rowIndex, Boolean isTrim) {
//isTrim默认为true
if (isTrim == null) {
isTrim = true;
}
if (setList != null && setList.containsKey(sheetIndex)) {
return setList.get(sheetIndex).get(rowIndex);
}
if (workbook != null) {
Map<Integer, String> cellDict = new HashMap<>();
Row row = workbook.getSheetAt(sheetIndex).getRow(rowIndex - 1);
for (int j = 0; j < row.getLastCellNum(); j++) {
//获取单元格的值
Cell cell = row.getCell(j);
String value = getCellValue(cell);
if (value != null && value.length() > 0) {
if (isTrim) {
cellDict.put(j, value.trim());
} else {
cellDict.put(j, value);
}
}
}
return cellDict;
}
return null;
}
/**
* 获取指定sheet表的实际行数
*
* @param sheetIndex 获取sheet表的Index
* @return
*/
public int getSheetLastRowNum(int sheetIndex) {
if (workbook != null && workbook.getNumberOfSheets() > 0) {
int row = workbook.getSheetAt(sheetIndex).getLastRowNum() + 1;
for (int i = 0; i < workbook.getSheetAt(sheetIndex).getLastRowNum() + 1; i++) {
if (workbook.getSheetAt(sheetIndex).getRow(i) == null) {
row--;
}
}
return row;
}
return 0;
}
/**
* 将所有excel文件数据都转换为string输出
*/
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
/*
_NONE(-1),
NUMERIC(0),
STRING(1),
FORMULA(2),
BLANK(3),
BOOLEAN(4),
ERROR(5);
*/
//判断数据的类型
switch (cell.getCellType()) {
case NUMERIC: //数字
cellValue = getNumericCellContent(cell);
break;
case STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: //Boolean
cellValue = getBooleanCellContent(cell);
break;
case FORMULA: //公式
cellValue = cell.getCellFormula();
break;
case BLANK: //空值
cellValue = "";
break;
case ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
/**
* 获取Boolean单元格的内容
*
* @param cell
* @return 若为真, 则返回1。若为假,则返回0。
*/
private static String getBooleanCellContent(Cell cell) {
String cellValue;
if (cell.getBooleanCellValue()) {
cellValue = "1";
} else {
cellValue = "0";
}
return cellValue;
}
/**
* 获取Numeric单元格的内容.若发生异常，则返回空！
* @param cell
* @return
*/
private static String getNumericCellContent(Cell cell) {
short format = cell.getCellStyle().getDataFormat();
if (!DateUtil.isCellDateFormatted(cell)) {
double numValue = cell.getNumericCellValue();
if (format == 33 && numValue < 1) {
numValue = numValue * 24 * 60 * 60;
int intValue = (int) numValue;
String str = "";
if (TimeUnit.SECONDS.toHours(intValue) > 0) {
long hours = TimeUnit.SECONDS.toHours(intValue);
long minutes = TimeUnit.SECONDS.toMinutes(intValue) % 60;
long seconds = intValue % 60;
str = hours + "时 " + minutes + "分 " + seconds + "秒";
} else if (TimeUnit.SECONDS.toMinutes(intValue) > 0) {
long minutes = TimeUnit.SECONDS.toMinutes(intValue);
long seconds = intValue % 60;
str = minutes + "分 " + seconds + "秒";
} else {
str = intValue + "秒";
}
return str;
} else {
// 修改 Excel 中对数字的精确位，从2位到6位,尚未测试是否对其他模块的影响
// 原来的格式 精确到小数后2位
// String numContent = String.format("%.2f", numValue);
// 若返回的数据结果后3位为".00",则去除。
// if (".00".equals(numContent.substring(numContent.length() - 3))) {
// numContent = numContent.substring(0, numContent.length() - 3);
// }
// 精确到后6位
String numContent = String.format("%.6f", numValue);
// 若返回的数据结果后7位为".000000",则去除。
if (".000000".equals(numContent.substring(numContent.length() - 7))) {
numContent = numContent.substring(0, numContent.length() - 7);
}
return numContent;
}
}
//若Numeric是日期而非数据
String dateContent = "";
Date dateValue;
try {
dateValue = cell.getDateCellValue(); //获取单元格的日期取值
} catch (Exception ex) {
ex.printStackTrace();
return "";
}
dateContent = GetCellDateString(format, dateValue);
return dateContent;
}
/**
* 时间格式统一
* @param numberFormatIndex
* @param value
* @return
*/
private static String GetCellDateString(int numberFormatIndex,Date value)
{
String content = "";
SimpleDateFormat sdf;
switch (numberFormatIndex)
{
case 14:
case 15:
case 16:
case 17:
case 31:
case 57:
sdf = new SimpleDateFormat("yyyy-MM-dd");
break;
case 18:
case 19:
case 20:
case 21:
case 32:
sdf = new SimpleDateFormat("HH:mm:ss");
break;
case 22:
case 164:
case 176:
case 177:
default:
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
break;
}
content = sdf.format(value);
return content;
}
// ----------------------------从Excel表格中读取图片信息---------------------------------
/**
* 返回指定sheet表中的所有的图片数据
* @param sheetIndex
* @return
*/
public List<ExcelPicturesInfo> getAllPictureInfos(int sheetIndex)
{
if (!StringUtils.isBlank(password)){
return null;
}
if (workbook == null) {initializeNPOIWorkbook();}
Sheet sheet = workbook.getSheetAt(sheetIndex); //获取Sheet对象
return this.getAllPictureInfos(sheet);
}
/**
* 根据文件类型,初始化workbook取值
*/
private void initializeNPOIWorkbook() {
File fileFs = new File(filePath); // 创建文件流
if (!password.trim().isEmpty()) {
try {
InputStream inp = new FileInputStream(fileFs);
POIFSFileSystem pfs = new POIFSFileSystem(inp);
inp.close();
EncryptionInfo encInfo = new EncryptionInfo(pfs);
Decryptor decryptor = Decryptor.getInstance(encInfo);
decryptor.verifyPassword(password);
workbook = new XSSFWorkbook(decryptor.getDataStream(pfs));
} catch (Exception e) {
throw new RuntimeException(e);
}
} else {
try {
workbook = WorkbookFactory.create(fileFs);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
formula = workbook.getCreationHelper().createFormulaEvaluator();
}
/**
* 返回指定sheet表中的所有的图片数据
* @param sheetName sheet表名称
* @return
*/
public List<ExcelPicturesInfo> getAllPictureInfos(String sheetName)
{
String[] SheetName = this.SheetNames();
for (int i = 0; i < SheetName.length; i++)
{
String name = SheetName[i].trim();
if (name.equals(sheetName.trim()))
{
return this.getAllPictureInfos(i);
}
}
return new ArrayList<>();
}
private List<ExcelPicturesInfo> getAllPictureInfos(Sheet sheet)
{
return getAllPictureInfos(sheet, null, null, null, null);
}
private List<ExcelPicturesInfo> getAllPictureInfos(Sheet sheet, Integer minRow, Integer maxRow, Integer minCol, Integer maxCol) {
Boolean onlyInternal = true;
if (sheet instanceof HSSFSheet) {
return getAllPictureInfos((HSSFSheet) sheet, minRow, maxRow, minCol, maxCol,onlyInternal);
} else if (sheet instanceof XSSFSheet) {
return getAllPictureInfos((XSSFSheet) sheet, minRow, maxRow, minCol, maxCol,onlyInternal);
} else {
try {
throw new Exception("未处理类型，没有为该类型添加：GetAllPicturesInfos()扩展方法！");
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
private List<ExcelPicturesInfo> getAllPictureInfos(HSSFSheet sheet, Integer minRow, Integer maxRow, Integer minCol, Integer maxCol,Boolean onlyInternal) {
List<ExcelPicturesInfo> picturesInfoList = new ArrayList<>();
HSSFShapeContainer shapeContainer = sheet.getDrawingPatriarch();
if (shapeContainer != null) {
List<HSSFShape> shapeList = shapeContainer.getChildren();
for (HSSFShape shape : shapeList) {
if (shape instanceof HSSFPicture && shape.getAnchor() instanceof HSSFClientAnchor) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (isInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.getRow1(), anchor.getRow2(), anchor.getCol1(),anchor.getCol2(),onlyInternal)) {
picturesInfoList.add(new ExcelPicturesInfo(anchor.getRow1(), anchor.getRow2(), (int)anchor.getCol1(), (int)anchor.getCol2(), picture.getPictureData().getData()));
}
}
}
}
return picturesInfoList;
}
private List<ExcelPicturesInfo> getAllPictureInfos(XSSFSheet sheet, Integer minRow, Integer maxRow, Integer minCol, Integer maxCol,Boolean onlyInternal) {
List<ExcelPicturesInfo> picturesInfoList = new ArrayList<>();
List<POIXMLDocumentPart> documentPartList = sheet.getRelations();
for (POIXMLDocumentPart documentPart : documentPartList) {
if (documentPart instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) documentPart;
List<XSSFShape> shapeList = drawing.getShapes();
for (XSSFShape shape : shapeList) {
if (shape instanceof XSSFPicture) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getPreferredSize();
if (isInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.getRow1(), anchor.getRow2(), anchor.getCol1(), anchor.getCol2(),onlyInternal)) {
picturesInfoList.add(new ExcelPicturesInfo(anchor.getRow1(), anchor.getRow2(),(int)anchor.getCol1(),(int) anchor.getCol2(), picture.getPictureData().getData()));
}
}
}
}
}
return picturesInfoList;
}
private boolean isInternalOrIntersect(Integer rangeMinRow, Integer rangeMaxRow, Integer rangeMinCol, Integer rangeMaxCol,
int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol,Boolean onlyInternal) {
int _rangeMinRow = (rangeMinRow != null) ? rangeMinRow : pictureMinRow;
int _rangeMaxRow = (rangeMaxRow != null) ? rangeMaxRow : pictureMaxRow;
int _rangeMinCol = (rangeMinCol != null) ? rangeMinCol : pictureMinCol;
int _rangeMaxCol = (rangeMaxCol != null) ? rangeMaxCol : pictureMaxCol;
if (onlyInternal) {
return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow &&
_rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol);
} else {
return ((Math.abs(_rangeMaxRow - _rangeMinRow) + Math.abs(pictureMaxRow - pictureMinRow)) >= Math.abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) &&
((Math.abs(_rangeMaxCol - _rangeMinCol) + Math.abs(pictureMaxCol - pictureMinCol)) >= Math.abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol));
}
}
private final static String xls = "xls";
private final static String xlsx = "xlsx";
/*
* 读入excel文件，解析后返回
* @param file
* @throws IOException*/
public static List<Map<String,String>> readExcel(MultipartFile file) throws IOException {
//检查文件
checkFile(file);
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
List<Map<String,String>> list = new ArrayList<>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//检测前100行中列数最多的为标题行
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
/* System.out.println(firstRowNum);
System.out.println(lastRowNum);*/
if(lastRowNum <= 100){
firstRowNum = checkRow(sheet, firstRowNum, lastRowNum);
}else {
firstRowNum = checkRow(sheet, firstRowNum, 100+firstRowNum);
}
//循环除了第一行的所有行
for(int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
//获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
if (firstRowNum == -1){
continue;
}
//获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
if (lastRowNum == -1){
continue;
}
Map<String, String> cells = new HashMap<>();
int a = 0;
/* String[] cells = new String[row.getPhysicalNumberOfCells()];*/
//循环当前行
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
/* cells[cellNum] = getCellValue(cell);*/
cells.put("data" + a, getCellValue(cell));
a++;
}
list.add(cells);
}
}
workbook.close();
}
return list;
}
/*
* 读取excel文件的标题行,并返回
* @param file
* @throws IOException
* */
public static List<String> readTitle(MultipartFile file) throws IOException {
//检查文件
checkFile(file);
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//创建返回对象，为标题行赋值
List<String> result = new ArrayList<>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
if (firstRowNum == -1){
continue;
}
//检测前100行中列数最多的为标题行
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum == -1){
continue;
}
System.out.println(firstRowNum);
System.out.println(lastRowNum);
if(lastRowNum <= 100){
firstRowNum = checkRow(sheet, firstRowNum, lastRowNum);
}else {
firstRowNum = checkRow(sheet, firstRowNum, 100+firstRowNum);
}
//获得当前行
Row row = sheet.getRow(firstRowNum);
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
result.add(getCellValue(cell));
}
}
workbook.close();
}
return result;
}
/*
* 读取excel文件的标题行并检验其对应的数据类型,并返回
* @param file
* @throws IOException
* */
public static List<String> readTitleAndType(MultipartFile file) throws IOException {
//检查文件
checkFile(file);
//获得Workbook工作薄对象
Workbook workbook = getWorkBook(file);
//创建返回对象，为标题行赋值
List<String> result = new ArrayList<>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
if (firstRowNum == -1){
continue;
}
//检测前100行中列数最多的为标题行
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum == -1){
continue;
}
if(lastRowNum <= 100){
firstRowNum = checkRow(sheet, firstRowNum, lastRowNum);
}else {
firstRowNum = checkRow(sheet, firstRowNum, 100+firstRowNum);
}
//获得当前行
Row row = sheet.getRow(firstRowNum);
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
result.add(getCellValue(cell));
}
}
workbook.close();
}
return result;
}
//检测100行内哪一行为标题行
public static int checkRow(Sheet sheet, int firstRowNum, int i) {
//将最后结果封装到listMap集合中
/*Map<Integer,Integer> resultMap ;*/
//最大的列数
int numberMax = 0 ;
//符合条件的行数
int result = firstRowNum;
for(int rowNum = firstRowNum ; rowNum<= i; rowNum++){
//获得当前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getPhysicalNumberOfCells();
//列的条数
int cellSize =lastCellNum-firstCellNum;
//循环当前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
String cellValue = getCellValue(cell);
//判断当前单元格是否为空
if (null == cellValue || cellValue.equals("")){
cellSize--;
}
}
if(cellSize>numberMax){
/* resultMap = new HashMap<>();*/
numberMax = cellSize;
result = rowNum;
/* resultMap.put(rowNum,numberMax);*/
}
}
//进行处理
/* for (Map.Entry<Integer, Integer> entry : resultMap.entrySet()) {
if(entry.getValue()>numberMax){
result=entry.getValue();
}
}*/
return result;
}
public static void checkFile(MultipartFile file) throws IOException{
//判断文件是否存在
if(null == file){
throw new FileNotFoundException("文件不存在！");
}
//获得文件名
String fileName = file.getOriginalFilename();
//判断文件是否是excel文件
if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
throw new IOException(fileName + "不是excel文件");
}
}
public static Workbook getWorkBook(MultipartFile file) throws IOException {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith(xls)){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith(xlsx)){
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
throw new IOException("获得Workbook工作薄对象异常！");
}
return workbook;
}
/**
* 导出Excel
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static XSSFWorkbook getHSSFWorkbook(String sheetName,List<String> title,List<String[]> values, XSSFWorkbook wb){
// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
if(wb == null){
wb = new XSSFWorkbook();
}
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet(sheetName);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
XSSFRow row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
//声明列对象
XSSFCell cell = null;
//创建标题
for (int i = 0; i < title.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(title.get(i));
cell.setCellStyle(style);
}
//创建内容
for (int i = 0; i < values.size(); i++) {
row =sheet.createRow(i+1);
for (int i1 = 0; i1 < values.get(i).length; i1++){
//将内容按顺序赋给对应的列对象
row.createCell(i1).setCellValue(values.get(i)[i1]);
}
}
return wb;
}
}
ExcelPicturesInfo.java是处理excle图片的类，这里没使用到，感兴趣的可以看一下（代码写的有问题的地方还请各位大佬多多指教）：
package com.zhang;
import java.util.Arrays;
/**
* excel读取图片实体类的定义
*/
public class ExcelPicturesInfo {
private Integer MinRow ;
private Integer MaxRow ;
private Integer MinCol ;
private Integer MaxCol ;
private byte[] PictureData ;
/**
* Excel表格中的图片信息类
* @param minRow 位置开始行号
* @param maxRow 位置结束行号
* @param minCol 位置开始列号
* @param maxCol 位置结束列号
* @param pictureData 图片内容
*/
public ExcelPicturesInfo(Integer minRow, Integer maxRow, Integer minCol, Integer maxCol, byte[] pictureData) {
MinRow = minRow;
MaxRow = maxRow;
MinCol = minCol;
MaxCol = maxCol;
PictureData = pictureData;
}
public Integer getMinRow() {
return MinRow;
}
public void setMinRow(Integer minRow) {
MinRow = minRow;
}
public Integer getMaxRow() {
return MaxRow;
}
public void setMaxRow(Integer maxRow) {
MaxRow = maxRow;
}
public Integer getMinCol() {
return MinCol;
}
public void setMinCol(Integer minCol) {
MinCol = minCol;
}
public Integer getMaxCol() {
return MaxCol;
}
public void setMaxCol(Integer maxCol) {
MaxCol = maxCol;
}
public byte[] getPictureData() {
return PictureData;
}
public void setPictureData(byte[] pictureData) {
PictureData = pictureData;
}
@Override
public String toString() {
return "ExcelPicturesInfo{" +
"MinRow=" + MinRow +
", MaxRow=" + MaxRow +
", MinCol=" + MinCol +
", MaxCol=" + MaxCol +
", PictureData=" + Arrays.toString(PictureData) +
'}';
}
}
arguments.java，封装的一个实体类对象：
package com.zhang;
public class arguments {
private String arg0;
private String arg1;
public arguments() {
}
public arguments(String arg0, String arg1) {
this.arg0 = arg0;
this.arg1 = arg1;
}
@Override
public String toString() {
return "arguments{" +
"arg0='" + arg0 + '\'' +
", arg1='" + arg1 + '\'' +
'}';
}
public String getArg0() {
return arg0;
}
public void setArg0(String arg0) {
this.arg0 = arg0;
}
public String getArg1() {
return arg1;
}
public void setArg1(String arg1) {
this.arg1 = arg1;
}
}
在完成项目编码，用maven打包，如果想发给其他小伙伴，没有java环境的使用，可在网上搜索一下java的jar包如何打包为exe并且带java环境的那种，这个是有的，我发我女朋友就是用了这种方式，这里大家可以自行查找就吧分享具体链接了（PS：当时做的参考链接找不到了）。
ps：以上代码都是作者写的，大家可以免费使用，如果有写的不好的地方的，欢迎大家指出一起学习进步，当时为了这个需求也是没考虑算法性能这部分需求
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
