版权声明：转载原创文章请以超链接形式请注明原文章出处，尊重作者，尊重原创！
恰饭广告
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<ArrayList<String>> getExcel(String excel_url, int... args) throws IOException {
ArrayList<ArrayList<String>> 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<ArrayList<String>> 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<ArrayList<String>> ans = new ArrayList<ArrayList<String>>();
// 遍历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<String> curarr = new ArrayList<String>();
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<ArrayList<String>> 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<ArrayList<String>> ans = new ArrayList<ArrayList<String>>();
// 遍历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<String> curarr = new ArrayList<String>();
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<ArrayList<String>> 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<String> 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); // 微秒
}
}
相关jar包下载
原文链接：https://www.idaobin.com/archives/2056.html
让我恰个饭吧.ヘ(￣ω￣ヘ)
支付宝 ——————- 微信
恰饭广告
