java保存文件到数据库中_java程序上传excel文件，保存数据到数据库中

1 packagecontrollers;2

3 importjava.io.File;4 importjava.io.FileInputStream;5 importjava.io.IOException;6 importjava.io.InputStream;7 importjava.text.DecimalFormat;8 importjava.util.ArrayList;9 importjava.util.Date;10 importjava.util.HashMap;11 importjava.util.List;12 importjava.util.Map;13

14 importorg.apache.commons.io.FilenameUtils;15 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;16 importorg.apache.poi.ss.usermodel.Cell;17 importorg.apache.poi.ss.usermodel.CellValue;18 importorg.apache.poi.ss.usermodel.DateUtil;19 importorg.apache.poi.ss.usermodel.FormulaEvaluator;20 importorg.apache.poi.ss.usermodel.Row;21 importorg.apache.poi.ss.usermodel.Sheet;22 importorg.apache.poi.ss.usermodel.Workbook;23 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;24

25 importmodels.ResultInfo;26 importplay.Play;27 importplay.data.Upload;28 importplay.libs.Files;29

30 /**

31 *@author32 * @date 2018年6月7日 上午9:01:5233 * @Description34 */

35 public class UploadFiles extendsBaseController {36

37 //Excel 2003

38 private final static String XLS = "xls";39 //Excel 2007

40 private final static String XLSX = "xlsx";41 //分隔符

42 private final static String SEPARATOR = "|";43

44 static String projectpath =Play.applicationPath.getPath().toString();45 static String path = projectpath + "\\public\\Filebakup\\excelfile\\";46 static int pagesize = 15;47 static int errcount = 0;48 static String rownum = "";49

50 //上传excel数据

51 public static voiduploadexcelfile(String type, String filetype)52 throwsIOException {53 List files = null;54 errcount = 0;55 rownum = "";56 while (true) {57 files = (List) request.args.get("__UPLOADS");58 if (files != null) {59 break;60 }61 }62 List filepaths = new ArrayList();63 int sign = 0;64 for(Upload upload : files) {65 if (upload.getSize() > 0) {66 File f =upload.asFile();67 String fileName =f.getName();68 File storeFile = new File(path +fileName);69 String allpath = path +fileName;70 filepaths.add(allpath);71 Files.copy(f, storeFile);72 List list = null;73 list = exportListFromExcel(new File(allpath), 0, type);74 if (list.size() > 0) {75 sign += 1;76 }77 }78 }79 ResultInfo result = newResultInfo();80 if (sign > 0) {81 renderJSON(result.success("success", refreshExpire()));82 } else{83 renderJSON(result.error("error", refreshExpire()));84 }85 }86

87 public static List exportListFromExcel(File file, intsheetNum,88 String type) throwsIOException {89 return exportListFromExcel(newFileInputStream(file),90 FilenameUtils.getExtension(file.getName()), sheetNum, type);91 }92

93 public static ListexportListFromExcel(InputStream is,94 String extensionName, int sheetNum, String type) throwsIOException {95 Workbook workbook = null;96 if(extensionName.toLowerCase().equals(XLS)) {97 workbook = newHSSFWorkbook(is);98 } else if(extensionName.toLowerCase().equals(XLSX)) {99 workbook = newXSSFWorkbook(is);100 }101 returnexportListFromExcel(workbook, sheetNum, type);102 }103

104 private static ListexportListFromExcel(Workbook workbook,105 intsheetNum, String type) {106 Date uploadtime = newDate();107 Sheet sheet =workbook.getSheetAt(sheetNum);108 //解析公式结果

109 FormulaEvaluator evaluator =workbook.getCreationHelper()110 .createFormulaEvaluator();111 List list = new ArrayList();112 int minRowIx = sheet.getFirstRowNum() + 1;113 if (!"".equals(type) && type != null && type.equals("qlqd")) {114 minRowIx = minRowIx + 1;115 }116 int maxRowIx =sheet.getLastRowNum();117 for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {118 Row row =sheet.getRow(rowIx);119 int mnnumber = rowIx - 1;120 String mnnbs = "";121 if (mnnumber < 10) {122 mnnbs = "000" +mnnumber;123 } else if (mnnumber >= 10 && mnnumber < 100) {124 mnnbs = "00" +mnnumber;125 } else if (mnnumber >= 100 && mnnumber < 1000) {126 mnnbs = "0" +mnnumber;127 } else if (mnnumber >= 1000) {128 mnnbs = "" +mnnumber;129 }130 StringBuilder sb = newStringBuilder();131 short minColIx = (short) (row.getFirstCellNum());132 short maxColIx =row.getLastCellNum();133 for (short colIx = minColIx; colIx <= maxColIx; colIx++) {134 Cell cell = row.getCell(newInteger(colIx));135 CellValue cellValue =evaluator.evaluate(cell);136 String cellstr = "";137 if (cellValue == null) {138 sb.append(SEPARATOR + " ");139 continue;140 }141 //经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了142 //其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html

143 switch(cellValue.getCellType()) {144 caseCell.CELL_TYPE_BOOLEAN:145 sb.append(SEPARATOR +cellValue.getBooleanValue());146 break;147 caseCell.CELL_TYPE_NUMERIC:148 //这里的日期类型会被转换为数字类型，需要判别后区分处理

149 if(DateUtil.isCellDateFormatted(cell)) {150 sb.append(SEPARATOR151 +util.DateUtil.date2String(cell152 .getDateCellValue()));153 } else{154 sb.append(SEPARATOR + new DecimalFormat("#").format(cellValue.getNumberValue()));155 }156 break;157 caseCell.CELL_TYPE_STRING:158 sb.append(SEPARATOR +cellValue.getStringValue());159 break;160 caseCell.CELL_TYPE_FORMULA:161 break;162 caseCell.CELL_TYPE_BLANK:163 break;164 caseCell.CELL_TYPE_ERROR:165 break;166 default:167 break;168 }169 }170 if (!sb.toString()171 .trim()172 .equals("| | | | | | | | | | | | | | | | | | | | | | | | | | | |")) {173 if (!"".equals(type) && type != null && type.equals("ms")) {174 MSCaseInfoController.addmscase(sb.toString(), uploadtime);175 } else if (!"".equals(type) && type != null

176 && type.equals("xs")) {177 XSCaseInfoController.addxscase(sb.toString(), uploadtime);178 } else if (!"".equals(type) && type != null

179 && type.equals("ks")) {180 KSCaseInfoController.addkscase("部门受案号", sb.toString(),181 uploadtime);182 } else{183 PowerListController.addPowerList(sb.toString());184 }185 }186 list.add(sb.toString());187 }188 returnlist;189 }190

191 }