继上一篇Excel的模板下载后，就此更新企业开发中常常需要使用到的Excel的导入与导出。Excel的解析需要用到的是Apache下的一个操作Excel的工具POI，下面也会有详细介绍。
一、 POI简介
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
二、 HSSF概况
HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。HSSF为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。
三、 POI EXCEL文档结构类
HSSFWorkbook excel文档对象
HSSFSheet excel的sheet HSSFRow excel的行
HSSFCell excel的单元格 HSSFFont excel字体
HSSFName 名称 HSSFDataFormat 日期格式
HSSFHeader sheet头
HSSFFooter sheet尾
HSSFCellStyle cell样式
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
四、 EXCEL常用操作方法
1、 得到Excel常用对象
POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream("d:/test.xls"));
//得到Excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook(fs);
//得到Excel工作表对象
HSSFSheet sheet = wb.getSheetAt(0);
//得到Excel工作表的行
HSSFRow row = sheet.getRow(i);
//得到Excel工作表指定行的单元格
HSSFCell cell = row.getCell((short) j);
//得到单元格样式
cellStyle = cell.getCellStyle();
2、建立Excel常用对象
//创建Excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建Excel工作表对象
HSSFSheet sheet = wb.createSheet("new sheet");
//创建Excel工作表的行
HSSFRow row = sheet.createRow((short)0);
//创建单元格样式
cellStyle = wb.createCellStyle();
//创建Excel工作表指定行的单元格
row.createCell((short)0).setCellStyle(cellStyle);
//设置Excel工作表的值
row.createCell((short)0).setCellValue(1);
3、设置sheet名称和单元格内容
wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16);
cell.setEncoding((short) 1);
cell.setCellValue("单元格内容");
4、取得sheet的数目
wb.getNumberOfSheets();
5、 根据index取得sheet对象
HSSFSheet sheet = wb.getSheetAt(0);
6、取得有效的行数
int rowcount = sheet.getLastRowNum();
7、取得一行的有效单元格个数
row.getLastCellNum();
8、单元格值类型读写
//设置单元格为STRING类型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); //读取为数值类型的单元格内容 cell.getNumericCellValue();
9、设置列宽、行高
sheet.setColumnWidth((short)column,(short)width); row.setHeight((short)height);
10、添加区域，合并单元格
//合并从第rowFrom行columnFrom列 Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo ,(short)columnTo); // 到rowTo行columnTo的区域 sheet.addMergedRegion(region); //得到所有区域 sheet.getNumMergedRegions()
11、保存Excel文件
FileOutputStream fileOut = new FileOutputStream(path); wb.write(fileOut);
12、根据单元格不同属性返回字符串数值
public String getCellStringValue(HSSFCell cell) {
String cellValue = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING://字符串类型
cellValue = cell.getStringCellValue();
if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_NUMERIC: //数值类型
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA: //公式
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
default:
break;
}
return cellValue;
}
13、常用单元格边框格式
HSSFCellStyle style = wb.createCellStyle(); //下边框 style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED); //左边框 style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED); //右边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN); //上边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN);
14、设置字体和内容位置
HSSFFont f = wb.createFont();
//字号
f.setFontHeightInPoints((short) 11);
//加粗
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
style.setFont(f);
//左右居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//上下居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//单元格内容的旋转的角度
style.setRotation(short rotation);
HSSFDataFormat df = wb.createDataFormat();
//设置单元格数据格式
style1.setDataFormat(df.getFormat("0.00%"));
//给单元格设公式
cell.setCellFormula(string);
//单元格内容的旋转的角度
style.setRotation(short rotation);
15、插入图片
//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
ImageIO.write(bufferImg,"jpg",byteArrayOut);
//读进一个excel模版
FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");
fs = new POIFSFileSystem(fos);
//创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);
patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
16、调整工作表位置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFPrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short)1);
ps.setFitWidth((short)1);
17、设置打印区域
HSSFSheet sheet = wb.createSheet("Sheet1");
wb.setPrintArea(0, "$A$1:$C$2");
18、标注脚注
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFFooter footer = sheet.getFooter()
footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
19、在工作单中清空行数据，调整行位置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("row sheet");
// Create various cells and rows for spreadsheet.
// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
sheet.shiftRows(5, 10, -5);
20、选中指定的工作表
HSSFSheet sheet = wb.createSheet("row sheet");
heet.setSelected(true);
21、工作表的放大缩小
HSSFSheet sheet1 = wb.createSheet("new sheet");
sheet1.setZoom(1,2); // 50 percent magnification
22、头注和脚注
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFHeader header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
23、自定义颜色
HSSFCellStyle style = wb.createCellStyle(); style.setFillForegroundColor(HSSFColor.LIME.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font); cell.setCellStyle(style);
24、填充和颜色设置
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("X");
style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style);
25、强行刷新单元格公式
HSSFFormulaevaluator eval=new HSSFFormulaevaluator((HSSFWorkbook) wb);
private static void updateFormula(Workbook wb,Sheet s,int row){
Row r=s.getRow(row);
Cell c=null;
FormulaEcaluator eval=null;
if(wb instanceof HSSFWorkbook)
eval=new HSSFFormulaevaluator((HSSFWorkbook) wb);
else if(wb instanceof XSSFWorkbook)
eval=new XSSFFormulaevaluator((XSSFWorkbook) wb);
for(int i=r.getFirstCellNum();i
c=r.getCell(i);
if(c.getCellType()==Cell.CELL_TYPE_FORMULA)
eval.evaluateFormulaCell(c);
}
}
说明：Formulaevaluator提供了evaluateFormulaCell(Cell cell)方法，计算公式保存结果，但不改变公式。而evaluateInCell(Cell cell) 方法是计算公式，并将原公式替换为计算结果，也就是说该单元格的类型不在是Cell.CELL_TYPE_FORMULA而是Cell.CELL_TYPE_NUMBERIC。HSSFFormulaevaluator提供了静态方法evaluateAllFormulaCells(HSSFWorkbook wb) ，计算一个Excel文件的所有公式，用起来很方便。
-------------------------------------------poi 方法总结-------------------------------------------------------------------
1.设置不显示excel网格线
sheet.setDisplayGridlines(false);其中sheet是Sheet对象
2.设置excel单元格中的内容换行
cellStyle.setWrapText(true);其中cellStyle是WorkBook创建的CellStyle对象,然后将cellStyle设置到要换行的Cell对象,最后在要换行的对象(一般为字符串)加入"/r/n"。如
topTile.append("/r/n" +“cellContent”);
3.单元格的合并
sheet.addMergedRegion(new CellRangeAddress(0, 4, 0, 2));本示例为合并4行2列
4.设置页眉和页脚的页数
HSSFHeader header = sheet.getHeader();
header.setCenter(“Center Header”);
header.setLeft(“Left Header”);
header.setRight(HSSFHeader.font(“Stencil-Normal”, “Italic”) +
HSSFHeader.fontSize((short) 16) + “Right w/ Stencil-Normal Italic font and size 16”);HSSFFooter footer = (HSSFFooter )sheet.getFooter()
footer.setRight( "Page " + HSSFFooter.page() + " of " +
HSSFFooter.numPages() );
5.使得一个Sheet适合一页
sheet.setAutobreaks(true);
6.设置放大属性(Zoom被明确为一个分数，例如下面的75%使用3作为分子，4作为分母)
sheet.setZoom(3,4);
7.设置打印
HSSFPrintSetup print = (HSSFPrintSetup) sheet.getPrintSetup();
print.setLandscape(true);//设置横向打印 print.setScale((short)
70);//设置打印缩放70%
print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//设置为A4纸张
print.setLeftToRight(true);//設置打印顺序先行后列,默认为先列行
print.setFitHeight((short) 10);设置缩放调整为10页高
print.setFitWidth((short) 10);设置缩放调整为宽高sheet.setAutobreaks(false); if (i != 0 && i % 30 == 0)
sheet.setRowBreak(i);//設置每30行分頁打印8.反复的行和列（设置打印标题） HSSFWorkbook wb = new HSSFWorkbook(); wb.setRepeatingRowsAndColumns(0, 0, 12, 1, 6);//设置1到12列,行1到6每一页重复打印
9.调整单元格宽度
sheet.setAutobreaks(true);
sheet.setColumnWidth((short)i,colsWidth[i]); //设定单元格长度
sheet.autoSizeColumn((short) i);//自动根据长度调整单元格长度
好了，下面我们进入正题，导入Excel。
导入Excel文件这是本文导入的Excel样例
分析 : 此Excel分为两部分，一是主表部分、二是子表部分，上面主表部分为码不多入库单的基本信息，下面子表为主表下的所有物资信息，所以我们要设计两张数据库表，来存储这些信息。我们在使用POI操作的时候也要分别读取主、子表的信息，它们基本上属于一对多的关系。
一、前端代码
1.导入按钮，绑定导入框，绑定事件方法，这里我将导入框隐藏了。页面显示的是导入按钮。
2.JS方法uploadFile() 因为导入的话，需要拿到用户选择导入的Excel文件，所以这里要写个上传文件的方法，让后端获取到，如果下面的代码看不懂也没关系，你只要CV就好了，把重要的地方改一改，比如url。这里的uploadfile是写在Vue的methods中。
uploadfile() {
var animateimg = $('#f8').val();
var imgarr = animateimg.split('\'); //分割
var myimg = imgarr[imgarr.length - 1]; //去掉 // 获取图片名
var houzui = myimg.lastIndexOf('.'); //获取 . 出现的位置
var ext = myimg.substring(houzui, myimg.length).toUpperCase(); //切割 . 获取文件后缀
//判断是否为Excel文件
if (ext != '.XLS' && ext != '.XLSX') {
alert("请上传excel文件！");
window.history.go(0)
} else {
var data = new FormData($("#form8")[0]);
//访问后端控制器
$.ajax({
url: "/materialNotpay/importRKD",
type: 'POST',
data: data,
cache: false,
processData: false,
contentType: false,
success: function (xhr111) {
window.history.go(0);
},
error: function () {
}
});
}
}
二、后端代码
说明 : 这里的后端信息我并没有采用实体类来存储信息，因为可能会出现一些问题，这里就不做过多解释了，这里使用的是Map集合与List集合来存储的信息数据，具体请看下面的代码演示。
1.首先我们要先引入POI的依赖
org.apache.poi
poi
3.17-beta1
org.apache.poi
poi-ooxml
3.17-beta1
2.Mybatis映射文件sql
说明: 因为我们如果想要把基本信息对应的多个物资列表显示到前端，就需要根据基本信息的id去判断获取对应外键的物资信息，所以这里获取了插入sql的id。
如下 :
useGeneratedKeys=“true” keyProperty=“id” keyColumn=“id”
insert into rkd_info
${key}
values
#{val}
insert into
rkd_wuziinfo(goods_serialNumbers,goods_code,goods_name,goods_type,goods_specifications,goods_brand,goods_source,goods_total,goods_price,goods_money,goods_date,goods_contractNumber,goods_validity,goods_annotation,rkd_goodsId)
values
#{val}
3.DAO层代码
void importRKD(@Param("map") Map map);
void importRKDWuZi(@Param("list") List arrayList);
4.Service层代码
//Service
void importRKD(Map map);
void importRKDWuZi(List arrayList);
实现类 :
@Override
public void importRKD(Map map) {
materialNoPayDao.importRKD(map);
}
@Override
public void importRKDWuZi(List arrayList) {
materialNoPayDao.importRKDWuZi(arrayList);
}
5.POI判断列中数据类型获取列值工具类
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import java.text.SimpleDateFormat;
import java.util.Date;
public class ExcelUtilTwo {
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
// 判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
//short s = cell.getCellStyle().getDataFormat();
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
// 验证short值
if (cell.getCellStyle().getDataFormat() == 14) {
sdf = new SimpleDateFormat("yyyy/MM/dd");
} else if (cell.getCellStyle().getDataFormat() == 21) {
sdf = new SimpleDateFormat("HH:mm:ss");
} else if (cell.getCellStyle().getDataFormat() == 22) {
sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
} else {
throw new RuntimeException("日期格式错误!!!");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getRichStringCellValue().getString());
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: // 公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: // 空值
cellValue = null;
break;
case Cell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
}
6.Controller控制器代码
说明 : 这里涉及到合并单元格的列读取，读取合并单元格的第一个坐标位置就可以获取到数据了。请求方法中的参数是拿到前端用户导入的Excel文件。
@PostMapping("importRKD")
@ResponseBody
public String importRKD(@RequestParam("file") MultipartFile file){
//先读主表，插主表
try {
//获取用户上传的Excel，并创建此Excel的工作簿对象
Workbook workbook = WorkbookFactory.create(file.getInputStream());
//创建集合用于存储Excel读出来的数据
Map map = new HashMap<>();
//使用工作薄对象，获取第一个Sheet页
Sheet sheetAt = workbook.getSheetAt(0);
//获取所有的合并单元格
List mergedRegions = sheetAt.getMergedRegions();
//主表共占用3行
//获取有内容的第一行，坐标为 (2,2) 代码中要减1
Row row1 = sheetAt.getRow(1);
//获取列
Cell row1Cell1 = row1.getCell(2);
Cell row1Cell2 = row1.getCell(6);
Cell row1Cell3 = row1.getCell(10);
//获取有内容的第二行
Row row2 = sheetAt.getRow(2);
//获取列
Cell row2Cell1 = row2.getCell(2);
Cell row2Cell2 = row2.getCell(6);
//获取列的内容(单号)
String row1CellContent1 = ExcelUtilTwo.getCellValue(row1Cell1);
//获取列的内容(金额)
String row1CellContent2 = ExcelUtilTwo.getCellValue(row1Cell2);
//获取列的内容(日期)
String row1CellContent3 = ExcelUtilTwo.getCellValue(row1Cell3);
//获取列的内容(制单人)
String row2CellContent1 = ExcelUtilTwo.getCellValue(row2Cell1);
//获取列的内容(联系电话)
String row2CellContent2 = ExcelUtilTwo.getCellValue(row2Cell2);
//判断用户信息是否填写完整
if (row1CellContent1!=null&&row1CellContent2!=null&&row1CellContent3!=null&&row2CellContent1!=null&&row2CellContent2!=null){
map.put("rkd_number",row1CellContent1);
map.put("rkd_money",row1CellContent2);
map.put("rkd_date",row1CellContent3);
map.put("rkd_person",row2CellContent1);
map.put("rkd_phone",row2CellContent2);
}
//定义变量提高作用域保存基本信息的自增id
String rkdInfoId = null;
//判断Map中是否有数据
if (map.size()!=0){
//添加到码不多入库单基本信息表中
RKDService.importRKD(map);
//获取其自增的id
Object obj = map.get("id");
String i = String.valueOf(obj);
//给上面的变量赋值
rkdInfoId = i;
}
//解析子表
//创建集合存储数据
List arrayList = new ArrayList();
//获取有效行数
int lastRowNum = sheetAt.getLastRowNum();
//遍历子表所有行
for (int numRow = 4; numRow < lastRowNum+1; numRow++){
//创建集合存储每一行数据
linkedList list = new linkedList();
//创建遍历的当前行
Row row = sheetAt.getRow(numRow);
//获取有效列数
short lastCellNum = row.getLastCellNum();
//遍历子表当前行所有的列
for (int numCell = 0; numCell < lastCellNum; numCell++){
//获取所有列
Cell cell = row.getCell(numCell);
//获取列内容
String cellValue = ExcelUtilTwo.getCellValue(cell);
//如果内容为null跳出，执行下一次循环
if (cellValue == null||cellValue.equals("")){
continue;
}
//将列数据添加到集合
list.add(cellValue);
//判断是否遍历到当前行的最后一列，在最后一列给其添加外键id绑定基础信息表
if (numCell == lastCellNum-1){
//给其添加外键id
list.add(rkdInfoId);
}
}
//行数据添加到集合
arrayList.add(list);
}
//当物资子表并且基本信息也有数据的时候执行插入
if (map.size()!=0&&arrayList.size()!=0){
//将数据插入到物资子表
Service.importRKDWuZi(arrayList);
}
} catch (Exception e) {
e.printStackTrace();
}
//跳转到页面显示信息，此处也是跳转的Controller，那个Contorller就是跳到页面将导入的信息显示出来我就不贴了，没什么实际意义。
return "/info/mabuduo";
}
效果 :
name为null是因为没有没使用到这一列。下面只需要根据业务需求将解析到数据库中的数据取出显示到页面上就可以了。
本文要导出的信息列表图
导出Excel后的效果图
分析 : 此Excel分为两部分，一是主表部分、二是子表部分，这些信息都是从数据库中查出来的，我们需要先查主表，然后再查子表部分。一般如果开发中需要导出Excel也会有一个Excel的格式规范。这些格式规范是固定死的，所以下面的讲解代码中会频繁的使用到索引与Excel的坐标位置。
一、前端代码
这里注意 : 我们需要传递主表的id到后端，这样后端才能定位到具体的导入项，从而去根据这个主表的id去写sql查DB。
exportExcel(materialId){
//定义url，并携带主表的id参数
let url="/mabuduo/exportRkdList?materialId="+materialId;
//判断是否有id为_exportForm的form表单，如果没有则创建一个隐藏的form，把url放入，然后submit
let exportForm = document.getElementById("_exportForm");
if (!exportForm){
exportForm = document.createElement("form");
exportForm.setAttribute('id',"_exportForm");
exportForm.setAttribute("action", url);
exportForm.setAttribute("method", "post");
}
document.body.appendChild(exportForm);
exportForm.submit();
}
这里注意 : 这个事件中的参数就是上面的按钮传递的当前主表的id。
二、后端代码
这里我们从前往后讲
@RequestMapping("/exportRkdList")
public void exportRkdList(@RequestParam("materialId") int id ,HttpServletResponse response){
//查询入库单主表信息，返回映射到实体类
InitRkdBasicInfoModel rkdBasicInfo = materialNoPayService.findRkdBasicInfo(id);
//查询入库单子表物资信息(这里的""为此方法的必要参数，因为这个方法并不是我特意为了查子表的物资列表信息写的)
List
InitRkdBasicInfoModel findRkdBasicInfo(int id);
实现类
@Override
public InitRkdBasicInfoModel findRkdBasicInfo(int id) {
return materialNoPayDao.findRkdBasicInfo(id);
}
InitRkdBasicInfoModel findRkdBasicInfo(@Param("id") int id);
List
@Data
@NoArgsConstructor
public class InitRkdBasicInfoModel{
private int id;
private String rkd_number;
private String rkd_name;
private String rkd_money;
private String rkd_date;
private String rkd_person;
private String rkd_phone;
}
这里注意 : 这里的sql，只需要根据你的业务sql去写查询就好了，我只是给提供了个想法，最简单粗暴的方式的 select * from 。。。。但是我相信你不会这么去做。
总结其实导入，导出非常简单，无非就是利用POI去解析操作Excel，麻烦的地方就是Excel模板的格式，需要使用数字去找坐标定位，其实搞明白了API它们都是一样的东西。有问题可以评论，有时间的话，也会分享更多有用的文章 !
