java filehelper_Java ExcelHelper帮助类

最新推荐文章于 2023-04-14 11:17:46 发布

原创最新推荐文章于 2023-04-14 11:17:46 发布 · 482 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java filehelper

该博客介绍了如何使用Apache POI库在Java中读取和导出Excel文件。提供了两个方法，分别用于处理.xlsx和.xls文件格式，通过遍历工作表和行来读取数据，并将其转换为字符串列表。此外，还包含了一个方法用于将结果集转换为Excel文件进行导出。示例代码展示了如何调用这些方法读取指定列并打印结果。

部署运行你感兴趣的模型镜像

恰饭广告

poi4.0读取Excel

ExcelHelper.cs

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.sql.ResultSet;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.DateUtil;

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;

public class ExcelHelper {

/**

* 读取Excel

* @param excel_url文件地址

* @param args后面的参数代表需要输出哪些列，参数个数可以任意

* @return

* @throws IOException

public static ArrayList> getExcel(String excel_url, int... args) throws IOException {

ArrayList> arr = null;

if (excel_url.toLowerCase().endsWith("x")) {

arr = xlsx_reader(excel_url, args);

} else {

arr = xls_reader(excel_url, args);

}

return arr;

}

private static ArrayList> xlsx_reader(String excel_url, int... args) throws IOException {

// 读取xlsx文件

XSSFWorkbook xssfWorkbook = null;

// 寻找目录读取文件

File excelFile = new File(excel_url);

InputStream is = new FileInputStream(excelFile);

xssfWorkbook = new XSSFWorkbook(is);

if (xssfWorkbook == null) {

System.out.println("未读取到内容,请检查路径！");

return null;

}

ArrayList> ans = new ArrayList>();

// 遍历xlsx中的sheet

for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {

XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);

if (xssfSheet == null) {

continue;

}

// 对于每个sheet，读取其中的每一行

for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {

XSSFRow xssfRow = xssfSheet.getRow(rowNum);

if (xssfRow == null)

continue;

ArrayList curarr = new ArrayList();

for (int columnNum = 0; columnNum < args.length; columnNum++) {

XSSFCell cell = xssfRow.getCell(args[columnNum]);

curarr.add(Trim_str(getValue(cell)));

}

ans.add(curarr);

}

return ans;

}

private static ArrayList> xls_reader(String excel_url, int... args) throws IOException {

// 读取xlsx文件

HSSFWorkbook xssfWorkbook = null;

// 寻找目录读取文件

File excelFile = new File(excel_url);

InputStream is = new FileInputStream(excelFile);

xssfWorkbook = new HSSFWorkbook(is);

if (xssfWorkbook == null) {

System.out.println("未读取到内容,请检查路径！");

return null;

}

ArrayList> ans = new ArrayList>();

// 遍历xlsx中的sheet

for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {

HSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);

if (xssfSheet == null) {

continue;

}

// 对于每个sheet，读取其中的每一行

for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {

HSSFRow xssfRow = xssfSheet.getRow(rowNum);

if (xssfRow == null)

continue;

ArrayList curarr = new ArrayList();

for (int columnNum = 0; columnNum < args.length; columnNum++) {

HSSFCell cell = xssfRow.getCell(args[columnNum]);

curarr.add(Trim_str(getValue(cell)));

}

ans.add(curarr);

}

return ans;

}

// 判断后缀为xlsx的excel文件的数据类

private static String getValue(XSSFCell xssfRow) {

if (xssfRow == null) {

return null;

}

if (xssfRow.getCellType() == xssfRow.getCellType().BOOLEAN) {

return String.valueOf(xssfRow.getBooleanCellValue());

} else if (xssfRow.getCellType() == xssfRow.getCellType().NUMERIC) {

if (HSSFDateUtil.isCellDateFormatted(xssfRow)) {

short format = xssfRow.getCellStyle().getDataFormat();

SimpleDateFormat sdf = null;

if (format == 14 || format == 31 || format == 57 || format == 58 || (176 <= format && format <= 178)

|| (182 <= format && format <= 196) || (210 <= format && format <= 213) || (208 == format)) { // 日期

sdf = new SimpleDateFormat("yyyy-MM-dd");

} else if (format == 20 || format == 32 || format == 183 || (200 <= format && format <= 209)) { // 时间

sdf = new SimpleDateFormat("HH:mm");

} else {

xssfRow.setCellType(xssfRow.getCellType().STRING);

return xssfRow.getRichStringCellValue().toString().trim();

}

double value = xssfRow.getNumericCellValue();

Date date = DateUtil.getJavaDate(value);

if (date == null || "".equals(date)) {

return "";

}

String result = "";

try {

result = sdf.format(date);

} catch (Exception e) {

e.printStackTrace();

return "";

}

return result;

} else {

return String.valueOf(xssfRow.getNumericCellValue());

}

} else if (xssfRow.getCellType() == xssfRow.getCellType().BLANK

|| xssfRow.getCellType() == xssfRow.getCellType().ERROR) {

return "";

} else if (xssfRow.getCellType() == xssfRow.getCellType().FORMULA) {

return xssfRow.getCellFormula();

} else {

return String.valueOf(xssfRow.getStringCellValue());

}

private static String getValue(HSSFCell hssfRow) {

if (hssfRow == null) {

return null;

}

if (hssfRow.getCellType() == hssfRow.getCellType().BOOLEAN) {

return String.valueOf(hssfRow.getBooleanCellValue());

} else if (hssfRow.getCellType() == hssfRow.getCellType().NUMERIC) {

if (HSSFDateUtil.isCellDateFormatted(hssfRow)) {

short format = hssfRow.getCellStyle().getDataFormat();

SimpleDateFormat sdf = null;

if (format == 14 || format == 31 || format == 57 || format == 58 || (176 <= format && format <= 178)

|| (182 <= format && format <= 196) || (210 <= format && format <= 213) || (208 == format)) { // 日期

sdf = new SimpleDateFormat("yyyy-MM-dd");

} else if (format == 20 || format == 32 || format == 183 || (200 <= format && format <= 209)) { // 时间

sdf = new SimpleDateFormat("HH:mm");

} else {

hssfRow.setCellType(hssfRow.getCellType().STRING);

return hssfRow.getRichStringCellValue().toString().trim();

}

double value = hssfRow.getNumericCellValue();

Date date = DateUtil.getJavaDate(value);

if (date == null || "".equals(date)) {

return "";

}

String result = "";

try {

result = sdf.format(date);

} catch (Exception e) {

e.printStackTrace();

return "";

}

return result;

} else {

return String.valueOf(hssfRow.getNumericCellValue());

}

} else if (hssfRow.getCellType() == hssfRow.getCellType().BLANK

|| hssfRow.getCellType() == hssfRow.getCellType().ERROR) {

return "";

} else if (hssfRow.getCellType() == hssfRow.getCellType().FORMULA) {

return hssfRow.getCellFormula();

} else {

return String.valueOf(hssfRow.getStringCellValue());

}

/**

* 非法字符控制

* @param str

* @return

private static String Trim_str(String str) {

if (str == null)

return null;

str = str.replace("'", "‘");

str = str.replace(";", "；");

str = str.replace(",", "，");

str = str.replace("?", "？");

str = str.replace("

str = str.replace(">", "＞");

str = str.replace("(", "(");

str = str.replace(")", ")");

str = str.replace("@", "＠");

str = str.replace("=", "＝");

str = str.replace("+", "＋");

str = str.replace("*", "＊");

str = str.replace("&", "＆");

str = str.replace("#", "＃");

str = str.replace("%", "％");

str = str.replace("$", "＄");

str = str.replaceAll("[\\t\\n\\r]", ""); // \t为制表符 \n为换行 \r为回车

return str;

}

/**

* Excel导出

* @param rs

* @param outName

* @throws Exception

public static void resultToExcel(ResultSet rs, String outName) throws Exception {

XSSFWorkbook wb = new XSSFWorkbook();

XSSFSheet sheet = wb.createSheet("sheet");

XSSFRow row = sheet.createRow(0);

XSSFCell cell;

for (int j = 0; j < rs.getMetaData().getColumnCount(); ++j) {

String colName = rs.getMetaData().getColumnLabel(j + 1);

cell = row.createCell(j);

cell.setCellValue(colName);

}

int i = 0;

while (rs.next()) {

row = sheet.createRow(i + 1);

for (int j = 0; j < rs.getMetaData().getColumnCount(); ++j) {

String c = rs.getString(j + 1);

row.createCell(j).setCellValue(c);

}

++i;

}

FileOutputStream foStream = new FileOutputStream(outName);

wb.write(foStream);

foStream.flush();

foStream.close();

}

调用MainTest.java

import java.io.IOException;

import java.util.ArrayList;

public class MainTest {

public static void main(String[] args) {

// TODO Auto-generated method stub

long begintime = System.nanoTime();

String excelFileName = "C://Users//daobin//Desktop//4.xlsx";

ArrayList> arr = null;

try {

arr = ExcelHelper.getExcel(excelFileName, 0, 1, 2, 3); // 后面的参数代表需要输出哪些列，参数个数可以任意

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

for (int i = 0; i < arr.size(); i++) {

ArrayList row = arr.get(i);

for (int j = 0; j < row.size(); j++) {

System.out.print(arr.get(i).get(j) + " ");

}

System.out.println();

}

long endtime = System.nanoTime();

long costTime = (endtime - begintime) / 1000;

System.out.println("行数" + arr.size());

System.out.println("用时" + costTime); // 微秒

}