POI工具类
低版本的poi包是不支持xlsx的。因为项目需要，总结了一个poi的工具类，分享一下。
我这里用的是poi4.0：
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
其中，主要是读写表格，获取表格信息等方法，支持xls和xlsx，只要把知道文件路径即可
public static void main(String[] args)
{
try
{
PoiUtil eb = new PoiUtil("C:\\Users\\Administrator\\Desktop\\PathInfo.xlsx");
//读表格
List<List<String>> data = eb.readAll(0);
//写表格
eb.write(data,"newSheet");
//获取sheet页数量
System.out.println(eb.getSheetCount());
//获取表名
System.out.println(eb.getSheetName(0));
//合并单元格
eb.region("newSheet",0,2,1,3);
eb.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
完整代码：
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
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 java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
/**
* Excel 工具类
*
* @version 1.0 2016/01/27
*/
public class PoiUtil
{
public static void main(String[] args)
{
try
{
PoiUtil eb = new PoiUtil("C:\\Users\\Administrator\\Desktop\\PathInfo.xlsx");
//读表格
List<List<String>> data = eb.readAll(0);
//写表格
eb.write(data,"newSheet");
//获取sheet页数量
System.out.println(eb.getSheetCount());
//获取表名
System.out.println(eb.getSheetName(0));
//合并单元格
eb.region("newSheet",0,2,1,3);
eb.close();
}
catch (Exception e)
{
e.printStackTrace();
}
}
private Workbook workbook;
private File file;
private boolean isXlsx = false;
private String pattern;// 日期格式
/**
* @param path 目标路径
* @className PoiExcelUtil2
* @describe 构造方法
* @author 马广胜
* @date 2021-5-26 15:32
*/
public PoiUtil(String path)
{
try
{
if (path.endsWith(".xls"))
{
file = new File(path);
if (!file.exists())
{
file.createNewFile();
workbook = new HSSFWorkbook();
}
else
{
InputStream fis = new FileInputStream(file);
workbook = new HSSFWorkbook(fis);
fis.close();
}
}
else if (path.endsWith(".xlsx"))
{
file = new File(path);
isXlsx = true;
if (!file.exists())
{
file.createNewFile();
workbook = new SXSSFWorkbook(-1);
}
else
{
InputStream fis = new FileInputStream(file);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis);
workbook = new SXSSFWorkbook(xssfWorkbook, -1);
fis.close();
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
/**
* 根据后缀判断是否为 Excel 文件，后缀匹配xls和xlsx
*
* @param path 路径
* @return true OR false
*/
public static boolean isExcel(String path)
{
if (path == null)
{
return false;
}
return path.endsWith(".xls") || path.endsWith(".xlsx");
}
/**
* 设置日期格式
*/
public void setPattern(String pattern)
{
this.pattern = pattern;
}
@Override
public String toString()
{
return getClass().getSimpleName();
}
public String toString(int sheetIx)
{
return "第 " + (sheetIx) + "个sheet 页，名称： " + getSheetName(sheetIx) + "，共 " + getRowCount(sheetIx) + "行！";
}
/**
* 复制sheet页
*
* @param sheetNum sheet页序号
* @param count 复制数量
* @return 结果
*/
public boolean cloneSheet(int sheetNum, int count)
{
try
{
//xlsx格式。将SXSSFWorkbook转成XSSFWorkbook，再获取XSSFSheet
if (isXlsx)
{
XSSFWorkbook xssfWorkbook = ((SXSSFWorkbook) workbook).getXSSFWorkbook();
XSSFSheet sheet = xssfWorkbook.getSheetAt(sheetNum);
if (Objects.isNull(sheet))
{
return false;
}
for (int i = 0; i < count; i++)
{
xssfWorkbook.cloneSheet(sheetNum);
}
}
//xls格式,直接读取
else
{
Sheet sheet = workbook.getSheetAt(sheetNum);
if (Objects.isNull(sheet))
{
return false;
}
for (int i = 0; i < count; i++)
{
workbook.cloneSheet(sheetNum);
}
}
return true;
}
catch (Exception e)
{
e.printStackTrace();
return false;
}
}
/**
* 获取 sheet名称
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @return sheet名称
*/
public String getSheetName(int sheetIx)
{
return workbook.getSheetName(sheetIx);
}
/**
* 返回sheet 中的行数
*
* @param sheetIx 指定 Sheet 页，从 0 开始
*/
public int getRowCount(int sheetIx)
{
//xlsx格式。将SXSSFWorkbook转成XSSFWorkbook，再获取XSSFSheet
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
return sheet.getPhysicalNumberOfRows();
}
//xls格式,直接读取
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
return sheet.getPhysicalNumberOfRows();
}
}
/**
* 清空指定sheet页（先删除后添加并指定sheetIx）
*/
public boolean clearSheet(String sheetName)
{
int index = workbook.getSheetIndex(sheetName);
if (index != -1)
{
removeSheetAt(index);
}
workbook.createSheet(sheetName);
if (index != -1)
{
setSheetOrder(sheetName, index);
}
return true;
}
/**
* 设置sheet 页的索引
*
* @param sheetname Sheet 名称
* @param sheetIx 索引，从0开始
*/
public void setSheetOrder(String sheetname, int sheetIx)
{
workbook.setSheetOrder(sheetname, sheetIx);
}
/**
* 将数据追加到sheet页最后
*
* @param rowData 数据
* @param isAppend 是否追加,true 追加，false 重置sheet再添加
*/
public boolean write(List<List<String>> rowData, String sheetName, boolean isAppend)
{
if (isAppend)
{
int row = 0;
if (workbook.getSheetIndex(sheetName) == -1)
{
workbook.createSheet(sheetName);
}
else
{
Sheet sheet = workbook.getSheet(sheetName);
row = sheet.getLastRowNum() + 1;
}
return write(sheetName, rowData, row);
}
else
{
// 清空再添加
clearSheet(sheetName);
return write(sheetName, rowData, 0);
}
}
/**
* 将数据写入到 Excel 新创建的 Sheet 页
*
* @param rowData 数据
* @param sheetName 长度为1-31，不能包含后面任一字符: ：\ / ? * [ ]
*/
public boolean write(List<List<String>> rowData, String sheetName)
{
boolean flag = false;
try
{
if (workbook.getSheetIndex(sheetName) == -1)
{
workbook.createSheet(sheetName);
}
flag = write(sheetName, rowData, 0);
}
catch (Exception e)
{
e.printStackTrace();
}
return flag;
}
/**
* 将数据写入到 Excel 指定 Sheet 页指定开始行中,指定行后面数据向后移动
*
* @param rowData 数据
* @param sheetName 指定 Sheet 页，从 0 开始
* @param startRow 指定开始行，从 0 开始
*/
private boolean write(String sheetName, List<List<String>> rowData, int startRow)
{
try
{
Sheet sheet = workbook.getSheet(sheetName);
int dataSize = rowData.size();
if (sheet.getLastRowNum() > 0)
{
// 如果小于等于0，则一行都不存在
sheet.shiftRows(startRow, sheet.getLastRowNum(), dataSize);
}
CellStyle style = workbook.createCellStyle();
// // 内容居中 3.0版本
// style.setAlignment(ExtendedFormatRecord.CENTER);
// //垂直居中
// style.setVerticalAlignment(ExtendedFormatRecord.VERTICAL_CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
DataFormat format = workbook.createDataFormat();
// 内容样式 设置单元格内容格式是文本
style.setDataFormat(format.getFormat("@"));
for (int i = 0; i < dataSize; i++)
{
Row row = sheet.createRow(i + startRow);
for (int j = 0; j < rowData.get(i).size(); j++)
{
Cell cell = row.createCell(j);
cell.setCellValue(rowData.get(i).get(j));
cell.setCellStyle(style);
}
}
return true;
}
catch (Exception e)
{
e.printStackTrace();
return false;
}
}
/**
* 设置cell 样式
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定行，从 0 开始
* @param colIndex 指定列，从 0 开始
* @param style 风格
*/
public boolean setStyle(int sheetIx, int rowIndex, int colIndex, CellStyle style)
{
try
{
Sheet sheet = workbook.getSheetAt(sheetIx);
Cell cell = sheet.getRow(rowIndex).getCell((short) colIndex);
cell.setCellStyle(style);
// sheet.autoSizeColumn((short) colIndex);
return true;
}
catch (Exception e)
{
e.printStackTrace();
return false;
}
}
/**
* 构建样式
*
* @param fontHeight 字体高度
* @param fontBold 字体粗度
* @param color 字体颜色
* @param border 边框是否加粗
*/
public CellStyle makeStyle(int fontHeight, int fontBold, int color, boolean border)
{
CellStyle style = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
// 内容样式 设置单元格内容格式是文本
style.setDataFormat(format.getFormat("@"));
// 内容居中
style.setAlignment(HorizontalAlignment.CENTER);
if (border)
{
// 边框样式
style.setBorderTop(BorderStyle.MEDIUM);
style.setBorderRight(BorderStyle.MEDIUM);
style.setBorderBottom(BorderStyle.MEDIUM);
style.setBorderLeft(BorderStyle.MEDIUM);
}
// 文字样式
Font font = workbook.createFont();
font.setFontHeight((short) fontBold);
font.setFontHeight((short) fontHeight);
font.setColor((short) color);
style.setFont(font);
return style;
}
/**
* 合并单元格
*
* @param sheetName 指定 Sheet 页，从 0 开始
* @param firstRow 开始行
* @param lastRow 结束行
* @param firstCol 开始列
* @param lastCol 结束列
*/
public void region(String sheetName, int firstRow, int lastRow, int firstCol, int lastCol)
{
if (lastRow == firstRow && lastCol == firstCol)
{
return;
}
Sheet sheet = workbook.getSheet(sheetName);
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, (short) firstCol, (short) lastCol));
CellStyle style = workbook.createCellStyle();
// 内容居中
style.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
Row row = sheet.getRow(firstRow);
Cell cell = row.getCell((short) firstCol);
cell.setCellStyle(style);
}
/**
* 读取指定sheet 页所有数据
*
* @param sheetIx 指定 sheet 页，从 0 开始
*/
public List<List<String>> readAll(int sheetIx)
{
return read(sheetIx, 0, getRowCount(sheetIx) - 1);
}
/**
* 读取指定sheet 页指定行数据
*
* @param sheetIx 指定 sheet 页，从 0 开始
* @param start 指定开始行，从 0 开始
* @param end 指定结束行，从 0 开始
*/
public List<List<String>> read(int sheetIx, int start, int end)
{
if (end > getRowCount(sheetIx))
{
end = getRowCount(sheetIx);
}
if (isXlsx)
{
return getXlsxList(sheetIx, start, end);
}
else
{
return getXlsList(sheetIx, start, end);
}
}
private List<List<String>> getXlsList(int sheetIx, int start, int end)
{
List<List<String>> list = new ArrayList<>();
Sheet sheet = workbook.getSheetAt(sheetIx);
int cols = sheet.getRow(0).getLastCellNum(); // 第一行总列数
for (int i = start; i <= end; i++)
{
List<String> rowList = new ArrayList<>();
Row row = sheet.getRow(i);
for (int j = 0; j < cols; j++)
{
if (row == null)
{
rowList.add(null);
continue;
}
rowList.add(getCellValueToString(row.getCell((short) j)));
}
list.add(rowList);
}
return list;
}
private List<List<String>> getXlsxList(int sheetIx, int start, int end)
{
List<List<String>> list = new ArrayList<>();
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
int cols = sheet.getRow(0).getLastCellNum(); // 第一行总列数
for (int i = start; i <= end; i++)
{
List<String> rowList = new ArrayList<>();
Row row = sheet.getRow(i);
for (int j = 0; j < cols; j++)
{
if (row == null)
{
rowList.add(null);
continue;
}
rowList.add(getCellValueToString(row.getCell(j)));
}
list.add(rowList);
}
return list;
}
/**
* 指定行是否为空
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定开始行，从 0 开始
* @return true 不为空，false 不行为空
*/
public boolean isRowNull(int sheetIx, int rowIndex)
{
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
return Objects.isNull(sheet.getRow(rowIndex));
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
return Objects.isNull(sheet.getRow(rowIndex));
}
}
/**
* 创建行，若行存在，则清空
*
* @param sheetIx 指定 sheet 页，从 0 开始
* 指定创建行，从 0 开始
*/
public boolean createRow(int sheetIx, int rowIndex)
{
try
{
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
XSSFRow row = sheet.getRow(rowIndex);
if (Objects.isNull(row))
{
sheet.createRow(rowIndex);
}
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
Row row = sheet.getRow(rowIndex);
if (Objects.isNull(row))
{
sheet.createRow(rowIndex);
}
}
return true;
}
catch (Exception e)
{
return false;
}
}
/**
* 指定单元格是否为空
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定开始行，从 0 开始
* @param colIndex 指定开始列，从 0 开始
* @return true 行不为空，false 行为空
*/
public boolean isCellNull(int sheetIx, int rowIndex, int colIndex)
{
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
if (Objects.isNull(sheet.getRow(rowIndex)))
{
return false;
}
XSSFRow row = sheet.getRow(rowIndex);
return Objects.isNull(row.getCell(colIndex));
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
if (Objects.isNull(sheet.getRow(rowIndex)))
{
return false;
}
Row row = sheet.getRow(rowIndex);
return Objects.isNull(row.getCell(colIndex));
}
}
/**
* 创建单元格
*
* @param sheetIx 指定 sheet 页，从 0 开始
* @param rowIndex 指定行，从 0 开始
* @param colIndex 指定创建列，从 0 开始
* @return true 列为空，false 行不为空
*/
public boolean createCell(int sheetIx, int rowIndex, int colIndex)
{
try
{
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
XSSFRow row = sheet.getRow(rowIndex);
if (Objects.isNull(row))
{
row = sheet.createRow(rowIndex);
}
XSSFCell cell = row.getCell(colIndex);
if (Objects.isNull(cell))
{
row.createCell(colIndex);
}
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
Row row = sheet.getRow(rowIndex);
if (Objects.isNull(row))
{
row = sheet.createRow(rowIndex);
}
Cell cell = row.getCell(colIndex);
if (Objects.isNull(cell))
{
row.createCell(colIndex);
}
}
return true;
}
catch (Exception e)
{
return false;
}
}
/**
* 返回所在行的列数
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定行，从0开始
* @return 返回-1 表示所在行为空
*/
public int getColumnCount(int sheetIx, int rowIndex)
{
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
XSSFRow row = sheet.getRow(rowIndex);
return row == null ? -1 : row.getLastCellNum();
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
Row row = sheet.getRow(rowIndex);
return row == null ? -1 : row.getLastCellNum();
}
}
/**
* 设置row 和 column 位置的单元格值
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定行，从0开始
* @param colIndex 指定列，从0开始
* @param value 值
*/
public boolean setValueAt(int sheetIx, int rowIndex, int colIndex, String value)
{
try
{
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
XSSFRow row = sheet.getRow(rowIndex);
if (Objects.isNull(row))
{
row = sheet.createRow(rowIndex);
}
XSSFCell cell = row.getCell(colIndex);
if (Objects.isNull(cell))
{
cell = row.createCell(colIndex);
}
cell.setCellValue(value);
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
Row row = sheet.getRow(rowIndex);
if (Objects.isNull(row))
{
row = sheet.createRow(rowIndex);
}
Cell cell = row.getCell(colIndex);
if (Objects.isNull(cell))
{
cell = row.createCell(colIndex);
}
cell.setCellValue(value);
}
return true;
}
catch (Exception e)
{
return false;
}
}
/**
* 返回 row 和 column 位置的单元格值
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定行，从0开始
* @param colIndex 指定列，从0开始
*/
public String getValueAt(int sheetIx, int rowIndex, int colIndex)
{
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
return getCellValueToString(sheet.getRow(rowIndex).getCell(colIndex));
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
return getCellValueToString(sheet.getRow(rowIndex).getCell((short) colIndex));
}
}
/**
* 重置指定行的值
*
* @param rowData 数据
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定行，从0开始
*/
public boolean setRowValue(int sheetIx, List<String> rowData, int rowIndex)
{
try
{
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
XSSFRow row = sheet.getRow(rowIndex);
if (Objects.isNull(row))
{
row = sheet.createRow(rowIndex);
}
for (int i = 0; i < rowData.size(); i++)
{
XSSFCell cell = row.getCell(i);
if (Objects.isNull(cell))
{
cell = row.createCell(i);
}
cell.setCellValue(rowData.get(i));
}
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
Row row = sheet.getRow(rowIndex);
if (Objects.isNull(row))
{
row = sheet.createRow(rowIndex);
}
for (int i = 0; i < rowData.size(); i++)
{
Cell cell = row.getCell(i);
if (Objects.isNull(cell))
{
cell = row.createCell(i);
}
cell.setCellValue(rowData.get(i));
}
}
return true;
}
catch (Exception e)
{
e.printStackTrace();
return false;
}
}
/**
* 返回指定行的值的集合
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定行，从0开始
*/
public List<String> getRowValue(int sheetIx, int rowIndex)
{
List<String> list = new ArrayList<>();
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
XSSFRow row = sheet.getRow(rowIndex);
if (row == null)
{
list.add(null);
}
else
{
for (int i = 0; i < row.getLastCellNum(); i++)
{
list.add(getCellValueToString(row.getCell(i)));
}
}
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
Row row = sheet.getRow(rowIndex);
if (row == null)
{
list.add(null);
}
else
{
for (int i = 0; i < row.getLastCellNum(); i++)
{
list.add(getCellValueToString(row.getCell((short) i)));
}
}
}
return list;
}
/**
* 返回列的值的集合
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定行，从0开始
* @param colIndex 指定列，从0开始
*/
public List<String> getColumnValue(int sheetIx, int rowIndex, int colIndex)
{
List<String> list = new ArrayList<>();
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
for (int i = rowIndex; i < getRowCount(sheetIx); i++)
{
XSSFRow row = sheet.getRow(i);
if (row == null)
{
list.add(null);
continue;
}
list.add(getCellValueToString(row.getCell(colIndex)));
}
}
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
for (int i = rowIndex; i < getRowCount(sheetIx); i++)
{
Row row = sheet.getRow(i);
if (row == null)
{
list.add(null);
continue;
}
list.add(getCellValueToString(row.getCell(colIndex)));
}
}
return list;
}
/**
* 获取excel 中sheet 总页数
*/
public int getSheetCount()
{
return workbook.getNumberOfSheets();
}
/**
* @className PoiExcelUtil2
* @describe 清除所有sheet页
* @author 马广胜
* @date 2021-5-26 15:49
*/
public void createSheet()
{
workbook.createSheet();
}
/**
* 设置sheet名称，长度为1-31，不能包含后面任一字符: ：\ / ? * [ ]
*
* @param sheetIx 指定 Sheet 页，从 0 开始，//
* @param name 名称
*/
public boolean setSheetName(int sheetIx, String name)
{
try
{
workbook.setSheetName(sheetIx, name);
}
catch (Exception e)
{
e.printStackTrace();
return false;
}
return true;
}
/**
* 获取sheet的索引，从0开始
*
* @param name sheet 名称
* @return -1表示该未找到名称对应的sheet
*/
public int getSheetIndex(String name)
{
return workbook.getSheetIndex(name);
}
/**
* 删除指定sheet
*
* @param sheetIx 指定 Sheet 页，从 0 开始
*/
public boolean removeSheetAt(int sheetIx)
{
try
{
workbook.removeSheetAt(sheetIx);
return true;
}
catch (Exception e)
{
return false;
}
}
/**
* 删除指定sheet中行，改变该行之后行的索引
*
* @param sheetIx 指定 Sheet 页，从 0 开始
* @param rowIndex 指定行，从0开始
*/
public boolean removeRow(int sheetIx, int rowIndex)
{
try
{
//xlsx格式。
if (isXlsx)
{
XSSFSheet sheet = ((SXSSFWorkbook) workbook).getXSSFWorkbook().getSheetAt(sheetIx);
sheet.shiftRows(rowIndex + 1, getRowCount(sheetIx), -1);
}
//xls格式
else
{
Sheet sheet = workbook.getSheetAt(sheetIx);
sheet.shiftRows(rowIndex + 1, getRowCount(sheetIx), -1);
Row row = sheet.getRow(getRowCount(sheetIx) - 1);
sheet.removeRow(row);
}
return true;
}
catch (Exception e)
{
e.printStackTrace();
return false;
}
}
public Workbook getWorkbook()
{
return workbook;
}
/**
* 关闭流
*/
public void close() throws IOException
{
OutputStream os = new FileOutputStream(file);
workbook.write(os);
os.flush();
os.close();
}
/**
* 转换单元格的类型为String 默认的 <br>
* 默认的数据类型：CELL_TYPE_BLANK(3), CELL_TYPE_BOOLEAN(4),
* CELL_TYPE_ERROR(5),CELL_TYPE_FORMULA(2), CELL_TYPE_NUMERIC(0),
* CELL_TYPE_STRING(1)
*/
private String getCellValueToString(XSSFCell cell)
{
String strCell = "";
if (cell == null)
{
return null;
}
switch (cell.getCellType().name())
{
case "BOOLEAN":
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case "NUMERIC":
if (DateUtil.isCellDateFormatted(cell))
{
Date date = cell.getDateCellValue();
if (pattern != null)
{
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
strCell = sdf.format(date);
}
else
{
strCell = date.toString();
}
break;
}
// 不是日期格式，则防止当数字过长时以科学计数法显示
// cell.setCellType(Cell.CELL_TYPE_STRING);
strCell = cell.toString();
break;
case "STRING":
strCell = cell.getStringCellValue();
break;
default:
break;
}
return strCell;
}
private String getCellValueToString(Cell cell)
{
String strCell = "";
if (cell == null)
{
return null;
}
switch (cell.getCellType().name())
{
case "BOOLEAN":
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case "NUMERIC":
if (DateUtil.isCellDateFormatted(cell))
{
Date date = cell.getDateCellValue();
if (pattern != null)
{
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
strCell = sdf.format(date);
}
else
{
strCell = date.toString();
}
break;
}
// 不是日期格式，则防止当数字过长时以科学计数法显示
// cell.setCellType(Cell.CELL_TYPE_STRING);
strCell = cell.toString();
break;
case "STRING":
strCell = cell.getStringCellValue();
break;
default:
break;
}
return strCell;
}
}
4115
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
