使用POI批量导入导出Excel-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_33417098/article/details/78131116

网上看到别人写的，然后稍作修改了一下，希望对大家有所帮助，添加了一些注释

项目中使用的是最新的 poi-3.14-20160307.jar,百度云地址：http://pan.baidu.com/s/1bnWFWg3 密码: kame

1.导出EXCEl数据表格，

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
* @author tang
*/
public class CreateSimpleExcelToDisk {

  /**
* poi导出excel数据表格
* @author : tangjiachao
* @since : 2017年7月1日
* @version : v0.0.1
*/
public static void main(String[] args) throws Exception {
    // 导出xls格式的excel
    // Workbook wb = new HSSFWorkbook();
    // 导出xlsx格式的excel
    Workbook wb = new XSSFWorkbook();
    DataFormat format = wb.createDataFormat();
    CellStyle style;
    // 创建一个SHEET
    Sheet sheet1 = wb.createSheet("产品清单");
    // 设置表头要显示的内容
    String[] title = {"编号", "产品名称", "产品价格", "产品数量", "生产日期", "产地", "是否出口"};
    int i = 0;
    // 创建一行
    Row row = sheet1.createRow((short) 0);
    // 填充标题.将标题放入第一行各列
    for (String s : title) {
      Cell cell = row.createCell(i);
      cell.setCellValue(s);
      i++;
    }

    // 创建第二行
    Row row1 = sheet1.createRow((short) 1);
    // 填充第二行第一列数据
    row1.createCell(0).setCellValue(20071001);
    // 填充第二行第二列数据
    // row1.createCell(1).setCellValue("金鸽瓜子");
    // 创建一个单元格子
    Cell cell2 = row1.createCell(2);
    // 填充产品价格
    cell2.setCellValue(2.45);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("#.##"));
    // 设定样式
    cell2.setCellStyle(style);
    // 填充产品数量
    row1.createCell(3).setCellValue(200);
    /*
     * 定义显示日期的公共格式
     * 如:yyyy-MM-dd hh:mm
     */
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    String newdate = sdf.format(new Date());
    // 填充出产日期
    row1.createCell(4).setCellValue(newdate);
    row1.createCell(5).setCellValue("陕西西安");
    // 显示布尔值
    row1.createCell(6).setCellValue(true);
    // 合并第二行第一列和第二列，此时value值为“金鸽瓜子的那一列要被注释掉”
    row1.getSheet().addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
    Row row2 = sheet1.createRow((short) 2);
    Cell cell3 = row2.createCell((short) 0);
    cell3.setCellValue("合并了三个单元格");
    // 真正合并的操作在这一句,起始行，结束行，起始列，结束列
    row2.getSheet().addMergedRegion(new CellRangeAddress(2, 2, 0, 2));
    FileOutputStream fileOut = new FileOutputStream("E:\\test.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

}

maven:

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-examples</artifactId>
      <version>3.14</version>
</dependency>
<dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
<version>3.1-b04</version>
</dependency>

2:导入EXCEL的数据处理

这是我的导入excel的jsp页面

<body>
<form method="post" id="test" enctype="multipart/form-data">
<p><input type="file" id="file1" name="uploadfile" /></p>
<input type="button" id="btnUpload" value="上传" />
</form>
<p><img id="img1" alt="上传成功啦" src="" /></p>
<script type="text/javascript">
$(function () {
$("#btnUpload").click(function(){
$('#test').form('submit', {
url: '<c:url value="page/datamgr/upload.do" />',
data:$('#file1').serialize(),
onSubmit: function(){
var isValid = $(this).form('validate');
if (!isValid){
}
return isValid; // 返回false将停止form提交
},
success: function(msg){
try{
alert('导入成功');
}catch(error){
alert('导入失败，请使用最新模板');
}
}
});
});
});
</script>
</body>

这是对应的控制器类的具体实现，由于隐私，所以导包之类，就不展示了，本人审美观不太好，勿喷

**
* 导入excel数据表格
* @author : tangjiachao
* @since : 2017年7月2日
* @version : v0.0.1
*/

public void upload(HttpServletRequest request, HttpServletResponse response){
Map<String,Object> map = new HashMap<String,Object>();
String tableName = request.getParameter("tableName");
String uploadDate = request.getParameter("uploadDate");
String dataType = request.getParameter("dataType");
String srcdataIdd=request.getParameter("srcdataIdd");
String datasouce=request.getParameter("souce");
Long srcdataId=Long.valueOf(srcdataIdd);//srcdataId srcdataId
//需要区分是源数据导入还是比对数据导入，在导入时传datasouce-2017-6-13 tangjiachao
// 转型为MultipartHttpRequest：
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
// 获得文件：
MultipartFile uploadfile = multipartRequest.getFile("uploadfile");
DataMgrProcess dmp = new DataMgrProcess(srcdataBatchnoService, srcdataRuleService, dataBaseService,compareDataRuleService);

try {
if (dataType != null){
if (dataType.equals(DATA_TYPE_TP)){
DataMgrResult result = dmp.uploadTemplateProcess(this, tableName, uploadfile, uploadDate,srcdataId,datasouce);
if (result.getFlag()){
map.put("result", "ok");
ResponseUtils.renderJson(response, JSONUtil.object2String(map));
} else {
map.put("result", "error");
map.put("message", result.getErrorMsg());
ResponseUtils.renderJson(response, JSONUtil.object2String(map));
}
} else if (dataType.equals(DATA_TYPE_SQL)){
DataMgrResult result = dmp.uploadSQLProcess(this, tableName, uploadfile, uploadDate ,request.getSession().getServletContext(), srcdataId,datasouce);
if (result.getFlag()){
map.put("result", "ok");
ResponseUtils.renderJson(response, JSONUtil.object2String(map));
} else {
map.put("result", "error");
map.put("message", result.getErrorMsg());
ResponseUtils.renderJson(response, JSONUtil.object2String(map));
}
}
}
} catch (Exception e) {
map.put("result", "error");
e.printStackTrace();
ResponseUtils.renderJson(response, JSONUtil.object2String(map));
}
}

下面是上面的控制器类调用的具体处理

/**
* @author Fren
*
*/
public class DataMgrProcess {
public final String databysouce = "databysouce";//源数据导入

public final String databycompare = "databycompare";//比对数据导如

public DataMgrResult uploadTemplateProcess(DataMgrController dmc, String tableName, MultipartFile uploadfile,
String uploadDate,Long srcdataId,String datasouce) throws Exception {
String fileName = uploadfile.getOriginalFilename();
// 查询批次版本 cjtanga改为按照多项目时每一个项目uploadVersion判断
//Integer uploadVersion = srcdataBatchnoService.getBatchnoVersion(tableName, uploadDate);

Integer uploadVersion=srcdataBatchnoService.getBatchnoVersionbynew(tableName, uploadDate, srcdataId,datasouce);
if(uploadVersion==null){//导入数据的版本
uploadVersion=1;
}else if(uploadVersion!=null){
uploadVersion=uploadVersion+1;
}

SysmgrUser user = dmc.getSysmgrUser();
// 生成批次数据
SrcdataBatchno srcdataBatchno = new SrcdataBatchno(dmc.getId(SrcdataBatchno.class), fileName,
DateUtil.parse(uploadDate, "yyyy-MM-dd"), user.getId(), uploadVersion, SrcdataBatchno.DATA_STATE_DRAFT,
DateUtil.parseTimestamp(new Date(), "yyyy-MM-dd HH:mm:ss"));

/* 解析并生成数据 */
InputStream is = uploadfile.getInputStream();
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow tittle = sheet.getRow(0);
if(datasouce.equals("databycompare")){//比对数据导入
tittle = sheet.getRow(1);
}
List<String> titleArray = new ArrayList<String>(); // 列头
List<List<String>> contextArray = new ArrayList<List<String>>();// 数据项内容
List<BatchnoDataDel> bddArray = new ArrayList<BatchnoDataDel>();// 中间表数据
// 设置ID
titleArray.add(DataBase.ID_FIELD);
// 读取列头
for (int i = 0; i < tittle.getLastCellNum(); ++i) {
HSSFCell cell = tittle.getCell(i);
titleArray.add(cell.getCellComment().getAuthor());
// System.out.println(cell.getCellComment().getAuthor() +
// "---"+cell.getCellCommen().geString());
}

/* 判断该模板是否为选择数据项的模板 */
DataBase db = new DataBase();
db.setTableName(tableName);
List<DataBase> list = dataBaseService.findByExample(db);
boolean flag = true;
String errorMsg = "";
for (DataBase dbObj : list) {
if (!titleArray.contains(dbObj.getColumnName())) {
flag = false;
errorMsg = "模板失效！";
break;
}
}
/* 判断是否超出上传限制 */
if (sheet.getLastRowNum() > dmc.UPLOAD_SIZE) {
flag = false;
errorMsg = "数据超过" + dmc.UPLOAD_SIZE + "条！";
}
if(sheet.getLastRowNum()==0){
//空文件只有模版--cjtanga
flag = false;
errorMsg = "导入的文件不能为空!";
}
Long startId = dmc.getBatchId(tableName, Long.valueOf(sheet.getLastRowNum()));
List<Long> idArray = null;
if (flag) {
idArray = new ArrayList<Long>();
// 读取内容
long start = System.currentTimeMillis();
long idtime = 0;
long celltime = 0;
int index = 1;
int lastRowNum = sheet.getLastRowNum();
if(datasouce.equals("databycompare")){//比对数据导入
lastRowNum = lastRowNum - 1;
index = 2;
}
for (int i = index; i <= sheet.getLastRowNum(); ++i) {
HSSFRow rows = sheet.getRow(i);
List<String> cellArray = new ArrayList<String>();
/* 生成数据表ID值 */
long idstart = System.currentTimeMillis();
Long dataId = (startId++);
long idend = System.currentTimeMillis();
idtime += (idstart - idend);

idArray.add(dataId);
cellArray.add(String.valueOf(dataId));
long cellstart = System.currentTimeMillis();
for (int j = 0; j < rows.getLastCellNum(); ++j) {
if (rows.getCell(j) != null) {
switch (rows.getCell(j).getCellType()) {
case Cell.CELL_TYPE_STRING:
rows.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
if(titleArray.get(j+1).trim().toString().equals("BIRTHDAY")){
String excelname=DataMgrProcess.this.unitdall(rows.getCell(j).toString(),titleArray.get(j+1).trim().toString());
cellArray.add(excelname);
}else{
cellArray.add(rows.getCell(j).getStringCellValue());
}
break;
case Cell.CELL_TYPE_NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(rows.getCell(j))) {
// rows.getCell(j).setCellType(Cell.CELL_TYPE_NUMERIC);
cellArray.add(DateUtil.dateFormat(rows.getCell(j).getDateCellValue(), "yyyy-MM-dd"));
} else {
short format=rows.getCell(j).getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
Date date=rows.getCell(j).getDateCellValue();
cellArray.add(DateUtil.dateFormat(date, "yyyy-MM-dd"));
}else if(format == 20 || format == 32){
cellArray.add(DateUtil.dateFormat(rows.getCell(j).getDateCellValue(), "HH:mm"));
}else if(titleArray.get(j+1).trim().toString().equals("BIRTHDAY")){
rows.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
String excelname=DataMgrProcess.this.unitdall(rows.getCell(j).toString(),titleArray.get(j+1).trim().toString());
cellArray.add(excelname);
}else{
rows.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
cellArray.add(String.valueOf(rows.getCell(j).getStringCellValue()));
}

}
break;
case Cell.CELL_TYPE_BLANK:
cellArray.add("");
break;
default:
rows.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
cellArray.add(rows.getCell(j).getStringCellValue());
}
} else {
cellArray.add("");
}
}
contextArray.add(cellArray);
long cellend = System.currentTimeMillis();
celltime += (cellstart - cellend);
/* 生成中间表数据 */
BatchnoDataDel batchnoDataDel = new BatchnoDataDel(dmc.getId(BatchnoDataDel.class),
srcdataBatchno.getId(), dataId,srcdataId, BatchnoDataDel.DATA_STATE_DRAFT);
bddArray.add(batchnoDataDel);
srcdataBatchno.setUploadNumber(lastRowNum);
}
long end = System.currentTimeMillis();
System.out.println("运行时间：" + (start - end) + "毫秒 , id:" + idtime + " cell:" + celltime);
}

// 查询数据源规则编号区分是源数据导入还是比对数据导如
if(datasouce.equals("databysouce")){//源数据导入
SrcdataRule srcdataRule = new SrcdataRule();
srcdataRule.setDbTable(tableName);
srcdataRule.setDeleteFlag((byte) 0);
srcdataRule = srcdataRuleService.findOneByExample(srcdataRule);
dataBaseService.saveContext(tableName , titleArray , contextArray , srcdataBatchno , bddArray ,idArray,srcdataRule.getFirstCheckClass(),srcdataRule);
}else{
CompareDataRule CompareDataRule = new CompareDataRule();
CompareDataRule.setDbTable(tableName);
CompareDataRule = compareDataRuleService.findOneByExample(CompareDataRule);
dataBaseService.saveContext11(tableName , titleArray , contextArray , srcdataBatchno , bddArray ,idArray,CompareDataRule.getFirstCheckClass(),CompareDataRule);
}

return new DataMgrResult(errorMsg, flag);
}