Java ExcelHelper帮助类

版权声明:转载原创文章请以超链接形式请注明原文章出处,尊重作者,尊重原创!


恰饭广告




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包下载

poi-4.0.1

原文链接:https://www.idaobin.com/archives/2056.html

让我恰个饭吧.ヘ( ̄ω ̄ヘ)

支付宝 ——————- 微信
图片加载中图片加载中



恰饭广告

发表评论

邮箱地址不会被公开。 必填项已用*标注

32 ÷ 4 =