在項目中用到了excel表格導入到資料庫,覺得很實用,分享給和我一樣碼代碼的碼農們
想把上傳的檔案導入到資料庫中,首先要先擷取到該檔案在伺服器中的路徑。
controller.java
String fileNames = filePath.replace("\\", "/");
File file = new File(fileNames);
InputStream is;
try {
is = new FileInputStream(file);
System.out.println(is);
System.out.println(is.available());
ExcelHelper help = new ExcelHelper();
List<String> temp = help.exportListFromExcel(is, fileNames, 0);
if(temp.size()==0){
}else if(temp.get(0).indexOf("error")>-1){
}
headcheckRegisterService.AssessExcel(temp);//添加到資料庫
} catch (IOException e) {
e.printStackTrace();
}
ExcelHelper.java
package com.roots.smp.manage.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.xssf.usermodel.XSSFWorkbook;
public class ExcelHelper {
private final static String XLS = "xls";
private final static String XLSX = "xlsx";
private final static String SEPARATOR = "|";
public static List<String> exportListFromExcel(File file, int sheetNum)
throws IOException {
return exportListFromExcel(new FileInputStream(file),
FilenameUtils.getExtension(file.getName()), sheetNum);
}
public static List<String> exportListFromExcel(InputStream is,String extensionName, int sheetNum) throws IOException {
Workbook workbook = null;
String end = extensionName.substring(extensionName.lastIndexOf(".")+1, extensionName.length()).toLowerCase();
if (end.equals(XLS)) {
workbook = new HSSFWorkbook(is);
} else if (end.equals(XLSX)) {
workbook = new XSSFWorkbook(is);
}
return exportListFromExcel(workbook, sheetNum);
}
private static List<String> exportListFromExcel(Workbook workbook, int sheetNum) {
SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
Sheet sheet = workbook.getSheetAt(sheetNum);
// 解析公式結果
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
List<String> list = new ArrayList<String>();
StringBuffer errorStr = new StringBuffer();
int minRowIx = sheet.getFirstRowNum();
int maxRowIx = sheet.getLastRowNum();
for (int rowIx = minRowIx+1; rowIx <= maxRowIx; rowIx++) {
Row row = sheet.getRow(rowIx);
StringBuilder sb = new StringBuilder();
int minColIx =0;
int maxColIx = row.getLastCellNum();
for (int colIx = minColIx; colIx <= maxColIx-1; colIx++) {
Cell cell = row.getCell(new Integer(colIx));
//String Scell=cell.toString();
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
errorStr.append("error:EXCEL檔案第[").append(rowIx+1).append("]行的第[").append(colIx+1).append("]列為空!<br>");
continue;
}
// 經過公式解析,最後隻存在Boolean、Numeric和String三種資料類型,此外就是Error了
// 其餘資料類型,根據官方文檔,完全可以忽略http://poi.apache.org/spreadsheet/eval.html
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
sb.append(SEPARATOR + cellValue.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
sb.append(SEPARATOR + df.format(cell.getDateCellValue()));
}else {
sb.append(SEPARATOR + cellValue.getNumberValue());
}
break;
case Cell.CELL_TYPE_STRING:
sb.append(SEPARATOR + cellValue.getStringValue());
break;
case Cell.CELL_TYPE_FORMULA:
sb.append(SEPARATOR + cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
break;
}
}
list.add(sb.toString());
}
if(errorStr.length()>0){
List<String> listerror = new ArrayList<String>();
listerror.add(errorStr.toString());
return listerror;
}
return list;
}
public static void main(String[] args) {
//String fileName = "D:/test.xls";
String fileName = "F:/tomcat7/apache-tomcat-7.0.72/webapps/AitaServer/tempfile/modulefile/phy/2016120913313132393.xlsx";
System.out.println(fileName);
File file = new File(fileName);
System.out.println(file);
InputStream is;
try {
is = new FileInputStream(file);
System.out.println(is);
System.out.println(is.available());
ExcelHelper help = new ExcelHelper();
List<String> temp = help.exportListFromExcel(is, fileName, 0);
System.out.println(temp.size());
for (String string : temp) {
System.out.println(string);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
這樣便完成了導入excel到資料庫的功能。。。。。。寫的不好,請多多指教