package com.chenxc.utils.excel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
/**
* 读写EXCEL文件
*/
public class POIUtils {
private static final String position_title = "title";
private static final String position_body = "body";
/**
* 判断excel版本
* @param in
* @param filename
* @return
* @throws IOException
*/
private static Workbook openWorkbook(InputStream in, String filename)
throws IOException {
Workbook wb = null;
if (filename.endsWith(".xlsx")) {
wb = new XSSFWorkbook(in);// Excel 2007
} else {
wb = new HSSFWorkbook(in);// Excel 2003
}
return wb;
}
/**
* 根据文件路径和工作薄下标导入Excel数据
* @param fileName 文件名
* @param sheetIndex 工作薄下标
* @return
* @throws Exception
*/
public static List<List<String>> getExcelData(MultipartFile file , String fileName, int sheetIndex) throws Exception {
List<List<String>> dataLst = new ArrayList<List<String>>();
Workbook wb = openWorkbook(file.getInputStream(), fileName);
Sheet sheet = (Sheet) wb.getSheetAt(sheetIndex);// 切换工作薄
Row row = null;
Cell cell = null;
int totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
int totalCells = totalRows >= 1 && sheet.getRow(0) != null ? sheet
.getRow(0).getPhysicalNumberOfCells() : 0;
for (int r = 1; r < totalRows; r++) {
row = sheet.getRow(r);
if (row == null || curRowInsideNull(row, totalCells))
continue;
List<String> rowLst = new ArrayList<String>();
for (int c = 0; c < totalCells; c++) {
cell = row.getCell(c);
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
int cellStyle = cell.getCellStyle().getDataFormat();
String cellStyleStr = cell
java 实现POI导入导出Excel
最新推荐文章于 2024-07-24 09:54:02 发布
本文详细介绍了如何使用Java的POI库进行Excel文件的导入与导出操作,包括创建工作簿、设置单元格数据、读取Excel内容等关键步骤,为Java开发者提供了实用的Excel处理技巧。
摘要由CSDN通过智能技术生成