Apache POI是Apache软件基金会的开源项目，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
有种业务叫做数据上报\数据汇总，通常是各个分支机构或相关组织，按模版或其他要求填报数据上传总部汇总。
本实践案例是用户在线下按Excel模版编辑数据，上传系统后，系统读取电子表格数据内容来完成数据上报工作。
对于Excel模版（XLS），要求如下：数据首行为数据列的定义，需要业务人员严格遵守，最后，以此数据列定义进行数据汇总、统计；其他可以任意定义。最后，按此模版出电子表格数据。
如下图所示的电子表格，第三行的C到J列，定义了表的数据列名，第四到第六行为数据内容。
读取电子表格的数据，首先滤除第一、二行及A、B列数据；其次先读取数据表的定义的数据列名，然后再读取数据内容。
处理过程
Created with Raphaël 2.1.0 开始 读取电子表格Sheet 按行读取Sheet 读取当前行单元格数据为列定义 读取当前行单元格数据 行结束？ 结束 yes no
MongoDB数据Schema定义
数据列 | 列名 | 说明 |
TableDef | 数据集合名称 | 规定上报数据报表 |
ReportDate | 上报数据时间 | 系统时间 |
ReportDN | 上报数据公司 | 系统DN |
Columns | 数据列 | 数据列数组 |
FiledName | 数据列名 | 一般表的第一行 |
oid | 数据列定义行的_id | 区别数据行和定义行 |
数据列名 | 数据 | 具体数据 |
如下图所示，程序运行结果（部分数据内容省略）。
package test;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import com.unicom.common.attachment.C_MONGODB;
public class TestExcel {
private class JsonKey{
private String key;
private int cellnum;
public String getKey(){
return key;
}
public int getCellNum(){
return cellnum;
}
public void setKey(String key){
this.key = key;
}
public void setCellNum(int num){
this.cellnum = num;
}
};
private static HSSFWorkbook readFile(String filename) throws IOException {
FileInputStream fis = new FileInputStream(filename);
try {
return new HSSFWorkbook(fis);
} finally {
fis.close();
}
}
public static void main(String[] args) {
JsonKey JKey[]=null; //定义表头
int flag = 0; //定义表头标志（一般是首行）
String oid = null ; //存储表头的MongoDB的“_id”
if (args.length < 1) {
System.err.println("At least one argument expected");
return;
}
String fileName = args[0];
try {
if (args.length < 2) {
HSSFWorkbook wb = TestExcel.readFile(fileName);
System.out.println("Data dump:\n");
for (int k = 0; k < wb.getNumberOfSheets(); k++) {
HSSFSheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s).");
StringBuffer tabledef = new StringBuffer();
tabledef.append("{\"TableDef\":\"" + wb.getSheetName(k) + "\",");
for (int r = 0; r < rows; r++) {
HSSFRow row = sheet.getRow(r);
//过滤空行
if (row == null) {
rows++;
continue;
}else{
if (flag==0){
int cells = row.getPhysicalNumberOfCells();
StringBuffer tableheader = new StringBuffer();
tableheader.append("\"Columns\":[");
JKey = new JsonKey[cells];
int cellnum = 0;
//此部门用于取表头
for (int c = 0; c < cells; c++) {
HSSFCell cell = row.getCell(c);
//过滤空列
if (cell == null){
cells++;
continue;
}
JKey[cellnum] = new TestExcel().new JsonKey();
String value = null;
value = cell.getStringCellValue();
JKey[cellnum].setKey(value);
JKey[cellnum].setCellNum(c);
tableheader.append("{\"FiledName\":\"" + value + "\"}");
tableheader.append(",");
cellnum ++;
}
tableheader.deleteCharAt(tableheader.length()-1);
tableheader.append("]}");
flag = 1;
tabledef.append(tableheader);
System.out.println("TableDef " + tabledef.toString());
//保存到MongoDB
oid = C_MONGODB.saveObjectByJson(tabledef.toString(),wb.getSheetName(k));
continue;
}
}
int cells = JKey.length;
StringBuffer datastr = new StringBuffer();
datastr.append("{\"oid\":\"" + oid + "\","); //标记本次导入数据的表头定义的_id
for (int c = 0; c < cells; c++) {
HSSFCell cell = row.getCell(JKey[c].getCellNum());
String value = null;
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
value = "\"" + JKey[c].getKey() + "\":\"" + cell.getCellFormula() + "\"";
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value = "\"" + JKey[c].getKey() + "\":\"" + cell.getNumericCellValue() + "\"";
break;
case HSSFCell.CELL_TYPE_STRING:
value = "\"" + JKey[c].getKey() + "\":\"" + cell.getStringCellValue() + "\"";
break;
default:
}
if (value == null){
value = "\"" + JKey[c].getKey() + "\":\"\"";;
}
datastr.append(value);
datastr.append(",");
}
datastr.deleteCharAt(datastr.length()-1);
datastr.append("}");
C_MONGODB.saveObjectByJson(datastr.toString(),wb.getSheetName(k));
System.out.println(datastr.toString());
}
}
wb.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}另附对MongoDB存储操作的代码。
/**
* 对于MongoDB 数据库操作的方法
*/
public class C_MONGODB {
/**
* 将数据存储到MongoDB中。
*
* @param json 要存储到MongoDB数据库的字符串
* @param collection MongoDB中集合的名称
* @return 插入数据的主键
* @throws Exception
*/
public static String saveObjectByJson(String json, String collection) throws Exception {
DBCollection coll = MongoDBUtil.getCollection(collection);
DBObject dbobject = (DBObject) JSON.parse(json);
coll.save(dbobject);
String oid = dbobject.get("_id").toString();
return oid;
}
}数据存储情况如下：
/* 37 */
{
"_id" : ObjectId("5735609ca705872048f795df"),
"TableDef" : "Sheet1",
"Columns" : [{
"FiledName" : "单位"
}, {
"FiledName" : "合计电量"
}, {
"FiledName" : "合计电费"
}, {
"FiledName" : "居民"
}, {
"FiledName" : "商业"
}, {
"FiledName" : "居民电费"
}, {
"FiledName" : "商业电费"
}, {
"FiledName" : "年月"
}]
}
/* 38 */
{
"_id" : ObjectId("5735609ca705872048f795e1"),
"oid" : "5735609ca705872048f795df",
"单位" : "XX公司",
"合计电量" : "1231.0",
"合计电费" : "3201.0",
"居民" : "",
"商业" : "231.0",
"居民电费" : "",
"商业电费" : "601.0",
"年月" : "201601.0"
}
/* 39 */
{
"_id" : ObjectId("5735609ca705872048f795e2"),
"oid" : "5735609ca705872048f795df",
"单位" : "YY饭店",
"合计电量" : "500.0",
"合计电费" : "2000.0",
"居民" : "200.0",
"商业" : "300.0",
"居民电费" : "1200.0",
"商业电费" : "2500.0",
"年月" : "201602.0"
}其中，第37条记录中的_id内容，与第38、39条记录中的oid对应，以此知道本次导入数据列的定义与数据内容关系。
注：开发功能时，还需要需要考虑数据版本的问题，需要提供数据删除功能。
参考：
1.HSSFReadWrite.java Apache POI - the Java API for Microsoft Documents “Reading or modifying an existing file”
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
