最近老大让我对账，看着每天几千条订单的表格，总感觉老大在背后让我乖乖站好。
为了偷懒，索性写了个方法完成对账的功能，明明能让机器做的事情，干嘛要人工呢。
废话不多说，直接撸代码：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.5-FINAL</version>
</dependency>/**
* 读写Excel 文件，支持office2003的xls文件和 office2007的xlsx文件。
*/
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
public class ExcelUtil {
public static boolean isXlsFile(String fileName) {
String excel2003Suffix = ".xls";
return (fileName.indexOf(excel2003Suffix) == fileName.length()
- excel2003Suffix.length());
}
public static boolean isXlsxFile(String fileName) {
String excel2007Suffix = ".xlsx";
return (fileName.indexOf(excel2007Suffix) == fileName.length()
- excel2007Suffix.length());
}
/**
* 检查是否是excel文件
*
* @param fileName 文件名
* @return true/false
*/
public static boolean isExcelFile(String fileName) {
if (StringUtils.isBlank(fileName)) {
return false;
}
return isXlsFile(fileName) || isXlsxFile(fileName);
}
/**
* 判断是否为空行
*
* @param row 行对象
* @return true 空 false 非空
*/
public static boolean isEmptyRow(Row row) {
if (row == null) {
return true;
}
boolean result = true;
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i, HSSFRow.RETURN_BLANK_AS_NULL);
String value = "";
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
value = String.valueOf((int) cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
value = String.valueOf(cell.getCellFormula());
break;
default:
break;
}
if (StringUtils.isNotBlank(value.trim())) {
result = false;
break;
}
}
}
return result;
}
/**
* 读取excel文件内容到数组。
*
* @param fileName excel文件名
* @return 每行数据放入一个数组A，多行数据生成的多个数组A再放入一个数组B，即数组的数组 B[A[]]
*/
public static ArrayList<Object> readXlsxFileToArray(String fileName) {
if (!isExcelFile(fileName)) {
System.out.println("readXlsxFileToArray: " + "不是excel文件");
return null;
}
ArrayList<Object> result = new ArrayList<Object>();
InputStream stream = null;
try {
stream = new FileInputStream(fileName);
Workbook wb = null;
if (isXlsFile(fileName)) {
wb = new HSSFWorkbook(stream);
} else if (isXlsxFile(fileName)) {
wb = new XSSFWorkbook(stream);
}
if (wb == null) {
System.out.println("readXlsxFileToArray: " + "文件打开失败");
return null;
}
Sheet sheet1 = wb.getSheetAt(0);
int maxCellNum = 0;
for (int i = 0; i <= sheet1.getLastRowNum(); i++) {
Row row = sheet1.getRow(i);
if (row == null || isEmptyRow(row)) {
break;
}
/**
* 最大列数由第一行列数决定,因为一般第一行为标题，后续行的列里面有空列
*/
if (i == 0) {
maxCellNum = row.getLastCellNum();
}
ArrayList<String> cellResult = new ArrayList<String>();
for (int j = 0; j < maxCellNum; j++) {
Cell cell = row.getCell(j);
String value = "";
if (cell == null) {
cellResult.add(value);
continue;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
value = sdf.format(date);
}else{
value = String
.valueOf((int) cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则，转化为Data格式
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
value = sdf.format(date);
}else{
value = String.valueOf(cell.getCellFormula());
}
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
case Cell.CELL_TYPE_ERROR:
System.out.println("readXlsxFileToArray: " + "错误单元格");
return null;
default:
break;
}
cellResult.add(value);
}
result.add(cellResult);
}
} catch (Exception e) {
System.out.println("readXlsxFileToArray: " + e.getMessage());
} finally {
try {
if (stream != null) {
stream.close();
}
} catch (IOException e) {
System.out.println("[readXlsxFileToArray]：关闭excel文件流异常:"
+ e.getMessage());
}
}
System.out.println("[readXlsxFileToArray] 完成");
return result;
}
}
测试方法：直接调用readXlsxFileToArray方法，获取的二维数组存入内存中，我这里做的操作是存到DB中，这样可以通过SQL进行关联操作。
List<Object> lists = ExcelUtil.readXlsxFileToArray(fileName);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
for (Object obj : lists) {
List<Object> list = (ArrayList<Object>) obj;
String id = (String) list.get(0);
String name = (String) list.get(3);
Byte r = Byte.parseByte((String) list.get(6));
Byte c = Byte.parseByte((String) list.get(7));
Byte s = Byte.parseByte((String) list.get(8));
String amountPaid = (String) list.get(10);
String dateStr = (String) list.get(12);
String contractName = (String) list.get(19);
String purchaseFundsId = (String) list.get(29);然后，我通过SQL查出对账后的结果，存在一个List<Map<String,Object>>中，再将其通过流写到硬盘上就ok啦，代码如下：
public void ExportExcel(){
//创建一个workbook对象
HSSFWorkbook wb = new HSSFWorkbook();
//根据workbook生成一个表sheet
HSSFSheet sheet = wb.createSheet("对账表");
//根据sheet获取第一行
HSSFRow row = sheet.createRow((int) 0);
//创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//向第一行单元格中set值
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("id");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("amount_paid");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("recharge_success");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("purchase_money");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("create_date");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("status");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue("name");
cell.setCellStyle(style);
cell = row.createCell((short) 7);
cell.setCellValue("contract_success");
cell.setCellStyle(style);
cell = row.createCell((short) 8);
cell.setCellValue("transfer_detail_id");
cell.setCellStyle(style);
cell = row.createCell((short) 9);
cell.setCellValue("recharge_record_id");
cell.setCellStyle(style);
//从数据库中获取需要导出的值
List<Map<String, Object>> list = orderHanderService.reconciliation();
for (int i = 0; i < list.size(); i++){
row = sheet.createRow((int) i + 1);
Map<String, Object> map = list.get(i);
//创建单元格，并设置值
row.createCell((short) 0).setCellValue((String) map.get("id"));
row.createCell((short) 1).setCellValue((String) map.get("amount_paid"));
row.createCell((short) 2).setCellValue(String.valueOf(map.get("recharge_success")));
row.createCell((short) 3).setCellValue(String.valueOf(map.get("purchase_money")));
cell = row.createCell((short) 4);
cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(map.get("create_date")));
row.createCell((short) 5).setCellValue(String.valueOf(map.get("status")));
row.createCell((short) 6).setCellValue((String) map.get("name"));
row.createCell((short) 7).setCellValue(map.get("contract_success")!=null?map.get("contract_success").toString():null);
row.createCell((short) 8).setCellValue((String) map.get("transfer_detail_id"));
row.createCell((short) 9).setCellValue((String) map.get("recharge_record_id"));
}
//使用流写文件
try {
FileOutputStream fout = new FileOutputStream("E:/order.xls");
wb.write(fout);
fout.close();
}
catch (Exception e) {
logger.error("异常", e);
}
}好啦，就是这样啦，程序员就是要偷懒，O(∩_∩)O哈哈~
扫一扫
408
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
