本项目基于SSM框架，简单封装了Excel批量导入导出功能，不用建数据库表一键导入导出Excel,不过这样只适用于对导入的Excel表进行转换。上一篇介绍了建表导入导出Excel（点击跳转）
一、下载poi jar包：
点这里下载：poi 3.8/3.9/3.10三个版本下载
poi 3.17最新版本下载
二、基本操作步骤：
首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（页/表）（HSSFSheet）组成，一个sheet是由多个row（行）（HSSFRow）组成，一个row是由多个cell（单元格）（HSSFCell）组成。
1、用HSSFWorkbook打开或者创建“Excel文件对象”
2、用HSSFWorkbook对象返回或者创建Sheet对象
3、用Sheet对象返回行对象，用行对象得到Cell对象
4、对Cell对象读写。
三、封装Excel工具类ImportExcelUtil
public class ImportExcelUtil {
private final static String Excel_2003 = ".xls"; //2003 版本的excel
private final static String Excel_2007 = ".xlsx"; //2007 版本的excel
/**
* @param in
* @param fileName
* @param columNum 自定义列数
* @return
* */
public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
List<List<Object>> list = null;
//创建Excel工作簿
Workbook work = this.getWorkbook(in, fileName);
if(work == null) {
throw new Exception("创建Excel工作簿为空！");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中的所有sheet
for(int i = 0; i<work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if(sheet == null) {continue;}
//遍历当前sheet中的所有行
//int totalRow = sheet.getPhysicalNumberOfRows();//如果excel有格式，这种方式取值不准确
int totalRow = sheet.getPhysicalNumberOfRows();
for(int j = sheet.getFirstRowNum(); j<totalRow; j++) {
row = sheet.getRow(j);
if(!isRowEmpty(row)) {
//if(row != null && !"".equals(row)) {
//获取第一个单元格的数据是否存在
Cell fristCell=row.getCell(0);
if(fristCell!=null){
//遍历所有的列
List<Object> li = new ArrayList<Object>();
//int totalColum = row.getLastCellNum();
for(int y = row.getFirstCellNum(); y<row.getLastCellNum(); y++) {
cell = row.getCell(y);
String callCal = this.getCellValue(cell)+"";
li.add(callCal);
}
list.add(li);
}
}else if(isRowEmpty(row)){
continue;
}
}
}
in.close();
return list;
}
/**
* 判断行是否为空
* @param row
* @return
*/
public static boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
return false;
}
return true;
}
/**
* 描述：根据文件后缀，自动适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
* */
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception {
Workbook work = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(Excel_2003.equals(fileType)){
work=new HSSFWorkbook(inStr);//2003 版本的excel
}else if(Excel_2007.equals(fileType)) {
work=new XSSFWorkbook(inStr);//2007 版本的excel
}else {
throw new Exception("解析文件格式有误！");
}
return work;
}
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
* */
public Object getCellValue(Cell cell) {
/*Object value = null;
DecimalFormat df1 = new DecimalFormat("0.00");//格式化number，string字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");//日期格式化
DecimalFormat df2 = new DecimalFormat("0.00");//格式化数字
if(cell !=null && !"".equals(cell)) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df1.format(cell.getNumericCellValue());
}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
}else if(HSSFDateUtil.isCellDateFormatted(cell)){
Date date = cell.getDateCellValue();
value = sdf.format(date);
}
else {
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
}
return value;*/
String result = new String();
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA: //Excel公式
try {
result = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException e) {
result = String.valueOf(cell.getRichStringCellValue());
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("M月d日");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#.##");
}
result = format.format(value);
}
break;
case HSSFCell.CELL_TYPE_STRING:// String类型
result = cell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
default:
result = "";
break;
}
return result;
}
public String getFormat(String str) {
if(str.equals("null")) {
str="";
return str;
}else{
return str;
}
}
public Integer getFormats(Integer str) {
if(str==null) {
str=0;
return str;
}else{
return str;
}
}
/**
* 获取字符串中的数字订单号、数字金额等，如从"USD 374.69"中获取到374.69、从“交易单号：66666666666”获取到66666666666
* @param receiptAmountString
* @return
*/
public static String getFormatNumber(String str){
str = str.trim();
Pattern p = Pattern.compile("[0-9]");
int indexNum = 0;
int lenght = str.length();
String num = "";
for(int i=0;i<lenght;i++){
num += str.charAt(i);
Matcher m = p.matcher(num);
if(m.find()){
indexNum = i;
break;
}
}
String formatNumber = str.substring(indexNum,lenght);
return formatNumber;
}
}
四、控制层Controller
解决：MultipartFile文件或者图片上传一直为null问题
/**
* 导出销售订单数据
* @param myFile
* @param respon
* @return
* @throws IOException
*/
@RequestMapping(value = "export", method=RequestMethod.POST)
public ModelAndView exportFile(@RequestParam(value="file",required=false)MultipartFile myFile,HttpServletResponse response,HttpServletRequest request)throws IOException {
ModelAndView mv = this.getModelAndView();
try {
ImportExcelUtil util = new ImportExcelUtil();
InputStream input = null;
List<List<Object>> lists = null;
if (myFile.isEmpty()) {
logBefore(logger, "导入文件为空，请先添加Excel文件！");
} else {
String fileName = myFile.getOriginalFilename();
input = myFile.getInputStream();
lists = util.getBankListByExcel(input, fileName);
input.close();
//导出用SXSSFWorkbook
SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
CellStyle style = workbook.createCellStyle();
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//SXSSFWorkbook方式的居中
// 创建一个sheet页
SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("销售订单");
// 创建标题
SXSSFRow headRow = (SXSSFRow) sheet.createRow(0);
headRow.createCell(0).setCellValue("单据日期");
headRow.createCell(1).setCellValue("单据编号");
headRow.createCell(2).setCellValue("客户编码");
headRow.createCell(3).setCellValue("客户");
headRow.createCell(4).setCellValue("结算客户编码");
headRow.createCell(5).setCellValue("结算客户");
headRow.createCell(6).setCellValue("部门编码");
headRow.createCell(7).setCellValue("部门");
headRow.createCell(8).setCellValue("业务员编码");
headRow.createCell(9).setCellValue("业务员");
headRow.createCell(10).setCellValue("币种代码");
headRow.createCell(11).setCellValue("币种");
headRow.createCell(12).setCellValue("汇率");
headRow.createCell(13).setCellValue("运输方式");
headRow.createCell(14).setCellValue("送货地址");
headRow.createCell(15).setCellValue("联系人");
headRow.createCell(16).setCellValue("联系电话");
headRow.createCell(17).setCellValue("客户手机号");
headRow.createCell(18).setCellValue("合同号");
headRow.createCell(19).setCellValue("收款方式");
headRow.createCell(20).setCellValue("外部单据号");
headRow.createCell(21).setCellValue("按仓库拆单出库");
headRow.createCell(22).setCellValue("国际单号");
headRow.createCell(23).setCellValue("内部单号");
headRow.createCell(24).setCellValue("备注");
headRow.createCell(25).setCellValue("仓库编码");
headRow.createCell(26).setCellValue("仓库");
headRow.createCell(27).setCellValue("项目编码");
headRow.createCell(28).setCellValue("项目");
headRow.createCell(29).setCellValue("存货编码");
headRow.createCell(30).setCellValue("存货名称");
headRow.createCell(31).setCellValue("销售单位");
headRow.createCell(32).setCellValue("数量");
headRow.createCell(33).setCellValue("报价");
headRow.createCell(34).setCellValue("折扣%");
headRow.createCell(35).setCellValue("税率%");
headRow.createCell(36).setCellValue("含税单价");
headRow.createCell(37).setCellValue("含税金额");
headRow.createCell(38).setCellValue("预计交货日期");
headRow.createCell(39).setCellValue("赠品");
headRow.createCell(40).setCellValue("备注");
headRow.createCell(41).setCellValue("国家");
headRow.createCell(42).setCellValue("货代");
headRow.createCell(43).setCellValue("客户单号");
// 循环将excel中的数据存入库
for (int i = 1; i < lists.size(); i++) {
List<Object> list = lists.get(i);
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(sheet.getLastRowNum() + 1);
dataRow.createCell(0).setCellValue(util.getFormat(String.valueOf(list.get(0))));//单据日期
dataRow.createCell(1).setCellValue(util.getFormat(String.valueOf(list.get(1))));//单据编号(内部单号)
dataRow.createCell(2).setCellValue(util.getFormat(String.valueOf(list.get(21))));//往来单位编码(客户编码)（结算客户编码）
dataRow.createCell(3).setCellValue("");//客户
dataRow.createCell(4).setCellValue(util.getFormat(String.valueOf(list.get(21))));//结算客户编码
dataRow.createCell(5).setCellValue("");//结算客户
dataRow.createCell(6).setCellValue(util.getFormat(String.valueOf(list.get(23))));//部门编码
dataRow.createCell(7).setCellValue("");//部门
dataRow.createCell(8).setCellValue(util.getFormat(String.valueOf(list.get(19))));//业务员编码
dataRow.createCell(9).setCellValue(util.getFormat(String.valueOf(list.get(20))));//业务员
dataRow.createCell(10).setCellValue(util.getFormat(String.valueOf(list.get(12))));//币种代码
dataRow.createCell(11).setCellValue("");//币种
dataRow.createCell(12).setCellValue(util.getFormat(String.valueOf(list.get(13))));//汇率
dataRow.createCell(13).setCellValue("");//运输方式
dataRow.createCell(14).setCellValue("");//送货地址
dataRow.createCell(15).setCellValue("");//联系人
dataRow.createCell(16).setCellValue("");//联系电话
dataRow.createCell(17).setCellValue("");//客户手机号
dataRow.createCell(18).setCellValue("");//合同号
dataRow.createCell(19).setCellValue("其它");//收款方式
dataRow.createCell(20).setCellValue("");//外部单据号
dataRow.createCell(21).setCellValue("");//按仓库拆单出库
dataRow.createCell(22).setCellValue("");//国际单号
dataRow.createCell(23).setCellValue(util.getFormat(String.valueOf(list.get(4))));//内部单号
dataRow.createCell(24).setCellValue(util.getFormat(String.valueOf(list.get(6))));//备注
dataRow.createCell(25).setCellValue(util.getFormat(String.valueOf(list.get(24))));//仓库编码
dataRow.createCell(26).setCellValue("");//仓库
dataRow.createCell(27).setCellValue("");//项目编码
dataRow.createCell(28).setCellValue("");//项目
dataRow.createCell(29).setCellValue(util.getFormat(String.valueOf(list.get(8))));//存货编码
dataRow.createCell(30).setCellValue("");//存货名称
dataRow.createCell(31).setCellValue("个");//销售单位
dataRow.createCell(32).setCellValue(util.getFormat(String.valueOf(list.get(9))));//数量
dataRow.createCell(33).setCellValue("");//报价
dataRow.createCell(34).setCellValue("");//折扣%
dataRow.createCell(35).setCellValue("0");//税率%
dataRow.createCell(36).setCellValue(util.getFormat(String.valueOf(list.get(10))));//含税单价
dataRow.createCell(37).setCellValue(util.getFormat(String.valueOf(list.get(11))));//含税金额
dataRow.createCell(38).setCellValue("");//预计交货日期
dataRow.createCell(39).setCellValue("");//赠品
dataRow.createCell(40).setCellValue("");//备注
dataRow.createCell(41).setCellValue(util.getFormat(String.valueOf(list.get(15))));//国家
dataRow.createCell(42).setCellValue(util.getFormat(String.valueOf(list.get(17))));//货代?国际
dataRow.createCell(43).setCellValue(util.getFormat(String.valueOf(list.get(5))));//客户单号
}
// 设置Excel文件名，并以中文进行编码
String codedFileName = new String("销售订单".getBytes("gbk"), "iso-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + DateUtil.getDays() +".xlsx");
// 响应类型,编码
response.setContentType("application/octet-stream;charset=UTF-8");
// 形成输出流
OutputStream osOut = response.getOutputStream();
// 将指定的字节写入此输出流
workbook.write(osOut);
// 刷新此输出流并强制将所有缓冲的输出字节被写出
osOut.flush();
// 关闭流
osOut.close();
/*
* dispose of temporary files backing this workbook on disk
* 处理在磁盘上备份此工作簿的临时文件 SXSSF分配临时文件，您必须始终清除显式，通过调用dispose方法
*/
workbook.dispose();
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("异常信息: " + e.getMessage() );
//发送错误到前台页面
mv.addObject("msg","error");
mv.setViewName("purchasing/orderExcel/orderExcelList");
return mv;
}
mv.addObject("msg","success");
return mv;
}
五、jsp页面
<form action="orderExcel/export.do" name="Form" id="Form" method="post" enctype="multipart/form-data">
<table id="table_report" class="table table-striped table-bordered table-hover">
<tr>
<td style="width:70px;text-align: right;padding-top: 13px;">选择Excel:</td>
<td><input id="uploadFile" name="file" type="file" style="width:330px" onchange="fileType(this)"/><br/><br/>
</td>
</tr>
<tr>
<td style="text-align: center;" colspan="10">
<input id="btnImportSubmit" class="btn btn-small btn-success" type="submit" value="Excel转换"/>
</td>
</tr>
</table>
</form><script type="text/javascript">
//接收错误信息判断！
var msg = '${msg}';
if(msg == 'error'){
alert("导出文件失败！请检查Excel文件！")
}
$(top.hangge());
//检索
function search(){
top.jzts();
$("#Form").submit();
}
//过滤类型
function fileType(obj){
var fileType=obj.value.substr(obj.value.lastIndexOf(".")).toLowerCase();//获得文件后缀名
if(fileType != '.xls' && fileType != '.xlsx'){
$("#uploadFile").tips({
side:3,
msg:'请选择正确的Excel文件',
bg:'#AE81FF',
time:3
});
$("#uploadFile").val('');
}
}
</script>
扫一扫
818
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
