對于批量資料的操作,在項目中引進Excel的導入和導出功能是個不錯的選擇。對于Excel表的結構,簡單了解可以把它分成三部分(Sheet,Cell,Row),這三部分可以了解為excel表中的頁,列,行。是以,我們想要擷取到某一個單元的内容,可以通過擷取該單元所在的頁數、對應所在的行和對應的列數進而定位到該機關,繼而便可執行操作進而擷取其中的内容。本文在SSM環境下基于Java的POI實作對excel的導入導出功能也是相似的思路。
準備工作:
導入POI對應的Jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14-beta1</version>
</dependency>
<dependency>
<groupId>org.apache.httpcomponents</groupId>
<artifactId>httpclient</artifactId>
<version>4.5.2</version>
</dependency>
建立一個ExcelBean實作對資料的封裝
public class ExcelBean implements java.io.Serializable{
private String headTextName; //列頭(标題)名
private String propertyName; //對應字段名
private Integer cols; //合并單元格數
private XSSFCellStyle cellStyle;
public ExcelBean(){
}
public ExcelBean(String headTextName, String propertyName){
this.headTextName = headTextName;
this.propertyName = propertyName;
}
public ExcelBean(String headTextName, String propertyName, Integer cols) {
super();
this.headTextName = headTextName;
this.propertyName = propertyName;
this.cols = cols;
}
/* 省略了get和set方法 */
}
建立一個Excel導入導出工具類ExcelUtil
public class ExcelUtil {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* Excel導入
*/
public static List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception{
List<List<Object>> list = null;
//建立Excel工作薄
Workbook work = getWorkbook(in,fileName);
if(null == work){
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中的所有行
//包涵頭部,是以要小于等于最後一列數,這裡也可以在初始值加上頭部行數,以便跳過頭部
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
//讀取一行
row = sheet.getRow(j);
//去掉空行和表頭
if(row==null||row.getFirstCellNum()==j){continue;}
//周遊所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(getCellValue(cell));
}
list.add(li);
}
}
return list;
}
/**
* 描述:根據檔案字尾,自适應上傳檔案的版本
*/
public static Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的檔案格式有誤!");
}
return wb;
}
/**
* 描述:對表格中數值進行格式化
*/
public static Object getCellValue(Cell cell){
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化字元類型的數字
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化數字
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 = df.format(cell.getNumericCellValue());
}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
value = sdf.format(cell.getDateCellValue());
}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;
}
/**
* 導入Excel表結束
* 導出Excel表開始
* @param sheetName 工作簿名稱
* @param clazz 資料源model類型
* @param objs excel标題列以及對應model字段名
* @param map 标題列行數以及cell字型樣式
*/
public static XSSFWorkbook createExcelFile(Class clazz, List objs, Map<Integer, List<ExcelBean>> map, String sheetName) throws
IllegalArgumentException,IllegalAccessException,InvocationTargetException,
ClassNotFoundException, IntrospectionException, ParseException {
// 建立新的Excel工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
// 在Excel工作簿中建一工作表,其名為預設值, 也可以指定Sheet名稱
XSSFSheet sheet = workbook.createSheet(sheetName);
// 以下為excel的字型樣式以及excel的标題與内容的建立,下面會具體分析;
createFont(workbook); //字型樣式
createTableHeader(sheet, map); //建立标題(頭)
createTableRows(sheet, map, objs, clazz); //建立内容
return workbook;
}
private static XSSFCellStyle fontStyle;
private static XSSFCellStyle fontStyle2;
public static void createFont(XSSFWorkbook workbook) {
// 表頭
fontStyle = workbook.createCellStyle();
XSSFFont font1 = workbook.createFont();
font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font1.setFontName("黑體");
font1.setFontHeightInPoints((short) 14);// 設定字型大小
fontStyle.setFont(font1);
fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下邊框
fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左邊框
fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上邊框
fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右邊框
fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
// 内容
fontStyle2=workbook.createCellStyle();
XSSFFont font2 = workbook.createFont();
font2.setFontName("宋體");
font2.setFontHeightInPoints((short) 10);// 設定字型大小
fontStyle2.setFont(font2);
fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下邊框
fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左邊框
fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上邊框
fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右邊框
fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
}
/**
* 根據ExcelMapping 生成列頭(多行列頭)
*
* @param sheet 工作簿
* @param map 每行每個單元格對應的列頭資訊
*/
public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map) {
int startIndex=0;//cell起始位置
int endIndex=0;//cell終止位置
for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {
XSSFRow row = sheet.createRow(entry.getKey());
List<ExcelBean> excels = entry.getValue();
for (int x = 0; x < excels.size(); x++) {
//合并單元格
if(excels.get(x).getCols()>1){
if(x==0){
endIndex+=excels.get(x).getCols()-1;
CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
sheet.addMergedRegion(range);
startIndex+=excels.get(x).getCols();
}else{
endIndex+=excels.get(x).getCols();
CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
sheet.addMergedRegion(range);
startIndex+=excels.get(x).getCols();
}
XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());
cell.setCellValue(excels.get(x).getHeadTextName());// 設定内容
if (excels.get(x).getCellStyle() != null) {
cell.setCellStyle(excels.get(x).getCellStyle());// 設定格式
}
cell.setCellStyle(fontStyle);
}else{
XSSFCell cell = row.createCell(x);
cell.setCellValue(excels.get(x).getHeadTextName());// 設定内容
if (excels.get(x).getCellStyle() != null) {
cell.setCellStyle(excels.get(x).getCellStyle());// 設定格式
}
cell.setCellStyle(fontStyle);
}
}
}
}
public static void createTableRows(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map, List objs, Class clazz)
throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,
ClassNotFoundException, ParseException {
int rowindex = map.size();
int maxKey = 0;
List<ExcelBean> ems = new ArrayList<>();
for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet()) {
if (entry.getKey() > maxKey) {
maxKey = entry.getKey();
}
}
ems = map.get(maxKey);
List<Integer> widths = new ArrayList<Integer>(ems.size());
for (Object obj : objs) {
XSSFRow row = sheet.createRow(rowindex);
for (int i = 0; i < ems.size(); i++) {
ExcelBean em = (ExcelBean) ems.get(i);
// 獲得get方法
PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);
Method getMethod = pd.getReadMethod();
Object rtn = getMethod.invoke(obj);
String value = "";
// 如果是日期類型進行轉換
if (rtn != null) {
if (rtn instanceof Date) {
value = DateUtils.formatDate((Date)rtn,"yyyy-MM-dd");
} else if(rtn instanceof BigDecimal){
NumberFormat nf = new DecimalFormat("#,##0.00");
value=nf.format((BigDecimal)rtn).toString();
} else if((rtn instanceof Integer) && (Integer.valueOf(rtn.toString())<0 )){
value="--";
}else {
value = rtn.toString();
}
}
XSSFCell cell = row.createCell(i);
cell.setCellValue(value);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(fontStyle2);
// 獲得最大列寬
int width = value.getBytes().length * 300;
// 還未設定,設定目前
if (widths.size() <= i) {
widths.add(width);
continue;
}
// 比原來大,更新資料
if (width > widths.get(i)) {
widths.set(i, width);
}
}
rowindex++;
}
// 設定列寬
for (int index = 0; index < widths.size(); index++) {
Integer width = widths.get(index);
width = width < 2500 ? 2500 : width + 300;
width = width > 10000 ? 10000 + 300 : width + 300;
sheet.setColumnWidth(index, width);
}
}
}
導入:
Excel表導入控制器層實作
@RequestMapping("/import")
public String impotr(HttpServletRequest request, Model model) throws Exception {
int adminId = 1;
//擷取上傳的檔案
MultipartHttpServletRequest multipart = (MultipartHttpServletRequest) request;
MultipartFile file = multipart.getFile("upfile");
String month = request.getParameter("month");
InputStream in = file.getInputStream();
//資料導入
salaryService.importExcelInfo(in,file,month,adminId);
in.close();
return "redirect:/salary/index.html";
}
Service層,這裡是接口importExcellnfo的實作方法,調用了ExcelUtil裡的方法
public void importExcelInfo(InputStream in, MultipartFile file, String salaryDate,Integer adminId) throws Exception{
List<List<Object>> listob = ExcelUtil.getBankListByExcel(in,file.getOriginalFilename());
List<Salarymanage> salaryList = new ArrayList<Salarymanage>();
//周遊listob資料,把資料放到List中
for (int i = 0; i < listob.size(); i++) {
List<Object> ob = listob.get(i);
Salarymanage salarymanage = new Salarymanage();
//設定編号
salarymanage.setSerial(SerialUtil.salarySerial());
//通過周遊實作把每一列封裝成一個model中,再把所有的model用List集合裝載
salarymanage.setAdminId(adminId);
salarymanage.setCompany(String.valueOf(ob.get(1)));
salarymanage.setNumber(String.valueOf(ob.get(2)));
salarymanage.setName(String.valueOf(ob.get(3)));
salarymanage.setSex(String.valueOf(ob.get(4)));
salarymanage.setCardName(String.valueOf(ob.get(5)));
salarymanage.setBankCard(String.valueOf(ob.get(6)));
salarymanage.setBank(String.valueOf(ob.get(7)));
//object類型轉Double類型
salarymanage.setMoney(Double.parseDouble(ob.get(8).toString()));
salarymanage.setRemark(String.valueOf(ob.get(9)));
salarymanage.setSalaryDate(salaryDate);
salaryList.add(salarymanage);
}
//批量插入
salarymanageDao.insertInfoBatch(salaryList);
}
接着是mapper.xml,用<foreach></foreach>實作資料批量插入
<insert id="insertInfoBatch" parameterType="java.util.List">
insert into salarymanage (admin_id, serial,company, number, name,sex, card_name, bank_card,
bank, money, remark,salary_date)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.adminId}, #{item.serial}, #{item.company},#{item.number}, #{item.name},
#{item.sex}, #{item.cardName},#{item.bankCard}, #{item.bank},
#{item.money}, #{item.remark}, #{item.salaryDate})
</foreach>
</insert>
到這裡,excel表的導入功能便完成了。這裡補充一下mybatis裡<foreach>裡面的部分參數,collection是傳入參數的類型,如果傳入參數是List,這裡便是list,如果是一個數組,便是array,separator指的是資料之間用“,”隔開,這也是借鑒了mysql插入多條資料的寫法,具體的執行效率還沒做多的探讨,我試過導入100條資料,效率還是可以接受的,如果有人有更好的寫法,歡迎留言交流。
導出:
Excel導出Controller端實作
@RequestMapping("/export")
public @ResponseBody void export(HttpServletRequest request, HttpServletResponse response) throwsClassNotFoundException, IntrospectionException, IllegalAccessException, ParseException, InvocationTargetException {
String salaryDate = request.getParameter("salaryDate");
if(salaryDate!=""){
response.reset(); //清除buffer緩存
Map<String,Object> map=new HashMap<String,Object>();
// 指定下載下傳的檔案名,浏覽器都會使用本地編碼,即GBK,浏覽器收到這個檔案名後,用ISO-8859-1來解碼,然後用GBK來顯示
// 是以我們用GBK解碼,ISO-8859-1來編碼,在浏覽器那邊會反過來執行。
response.setHeader("Content-Disposition", "attachment;filename=" + new String(salaryDate.getBytes("GBK"),"ISO-8859-1"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
XSSFWorkbook workbook=null;
//導出Excel對象
workbook = salaryService.exportExcelInfo(salaryDate);
OutputStream output;
try {
output = response.getOutputStream();
BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
bufferedOutPut.flush();
workbook.write(bufferedOutPut);
bufferedOutPut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
Service層,這裡是exportExcelInfo的實作方法
public XSSFWorkbook exportExcelInfo(String salaryDate) throws InvocationTargetException, ClassNotFoundException, IntrospectionException, ParseException, IllegalAccessException {
//根據條件查詢資料,把資料裝載到一個list中
List<Salarymanage> list = salarymanageDao.selectApartInfo(salaryDate);
for(int i=0;i<list.size();i++){
//查詢财務名字
int adminId = list.get(i).getAdminId();
String adminName = salarymanageDao.selectAdminNameById(adminId);
list.get(i).setAdminName(adminName);
list.get(i).setId(i+1);
}
List<ExcelBean> excel=new ArrayList<>();
Map<Integer,List<ExcelBean>> map=new LinkedHashMap<>();
XSSFWorkbook xssfWorkbook=null;
//設定标題欄
excel.add(new ExcelBean("序号","id",0));
excel.add(new ExcelBean("廠名","company",0));
excel.add(new ExcelBean("工号","number",0));
excel.add(new ExcelBean("姓名","name",0));
excel.add(new ExcelBean("性别","sex",0));
excel.add(new ExcelBean("開戶名","cardName",0));
excel.add(new ExcelBean("銀行卡号","bankCard",0));
excel.add(new ExcelBean("開戶行","bank",0));
excel.add(new ExcelBean("金額","money",0));
excel.add(new ExcelBean("備注","remark",0));
map.put(0, excel);
String sheetName = salaryDate + "月份收入";
//調用ExcelUtil的方法
xssfWorkbook = ExcelUtil.createExcelFile(Salarymanage.class, list, map, sheetName);
return xssfWorkbook;
}
這裡不寫出導出功能的mapper.xml實作語句了,具體實作也就是資料查詢,把查詢出來的資料轉載到一個List中。前端的話便是一個連結的請求,同時補充一點,ajax請求是不支援excel表導出的,是以對于導出時間較長,需要對導出成功做出判斷的可以選擇在後端生成json資料,在前端利用js進行excel表導出。
以上便是在SSM下使用POI實作excel表的導入和導出的整體思路,主要的導入和導出的核心方法都封裝在ExcelUtil這個工具類中,但面對具體的表格需要具體分析循環的開始,以便能夠去除表頭或者标題欄。