java 兼容excel_java中poi解析excel（兼容07版本以上及以下：.xls和.xlsx格式）

1 packagecom.genersoft.cbms.ysbz.ExcelDr.cmd;2

3 importcom.genersoft.cbms.ysbz.ExcelDr.dao.ExcelDrDao;4 importcom.genersoft.cbms.ysbz.ExcelDr.dao.IExcelDrDao;5 importcom.genersoft.cbms.ysbz.ExcelDr.domain.IExcelDrDomain;6 importcom.genersoft.cbms.ysbz.ExcelDr.entity.ExcelDr;7 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;8 importorg.apache.poi.ss.usermodel.Cell;9 importorg.apache.poi.ss.usermodel.Row;10 importorg.apache.poi.ss.usermodel.Sheet;11 importorg.apache.poi.ss.usermodel.Workbook;12 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;13 importorg.loushang.bsp.security.context.GetBspInfo;14 importorg.loushang.bsp.share.organization.OrganFactory;15 importorg.loushang.next.dao.DaoFactory;16 importorg.loushang.next.data.DataSet;17 importorg.loushang.next.data.ParameterSet;18 importorg.loushang.next.data.Record;19 importorg.loushang.next.upload.UploadFile;20 importorg.loushang.next.web.cmd.BaseAjaxCommand;21 importorg.loushang.sca.ScaComponentFactory;22

23 import java.io.*;24 importjava.net.ServerSocket;25 import java.util.*;26

27 /**

28 * Created by lyx on 2016/10/18.29 */

30 public class ExcelDrCommand extendsBaseAjaxCommand {31 private static IExcelDrDomain excelDomain = ScaComponentFactory.getService(IExcelDrDomain.class, "excelDomain/excelDomain");32 IExcelDrDao exceldao = (IExcelDrDao) DaoFactory.getDao(ExcelDrDao.class.getName());33

34 String dynm;35

36 /**

37 * excel导入38 */

39 public voidimportExcel() {40 //用来存插到bzsj表里的数据41 //List itemList = new ArrayList();

42 ExcelDr item = newExcelDr();43

44 //获取用户名称

45 String organ_id = GetBspInfo.getBspInfo().getCorporationOrganId();//组织ID

46 String organ_name;47 if (organ_id == null) {48 organ_name = "%";49 } else{50 organ_name = OrganFactory.getIOrganProvider().getOrganByOrganId(organ_id).getOrganName();//组织名称

51 }52 item.setFcsjUser(organ_id);53

54 String slnm = (String) getParameter("slnm");55 item.setFcsjSlnm(slnm);56 //前台选中的组织的内码编号和名称

57 String zznm = (String) getParameter("zznm");58 String zzbh = (String) getParameter("zzbh");59 String zzmc = (String) getParameter("zzmc");60 //是否选中按组织导入

61 Boolean ifcheck = (Boolean) getParameter("ifcheck");62 //前台选中的目标类型的内码编号和名称

63 String faMblx = (String) getParameter("faMblx");64 String mblxmc = (String) getParameter("mblxmc");65 String mblxbh = (String) getParameter("mblxbh");66 //获取前台选中了哪些报表

67 String[] bbbhs = (String[]) getParameter("bbbhs");68

69 //不按组织导入时，获取前台选中了哪些组织

70 String[] zzbhs = (String[]) getParameter("zzbhs");71

72 //解析excel

73 Record[] records = (Record[]) getParameter("records");74 if (records == null || records.length < 1)75 return;76 Record record = records[0];77 UploadFile file = (UploadFile) record.get("file");78 InputStream inputStream = null;79 try{80 inputStream =file.getInputStream();81 } catch(FileNotFoundException e) {82 e.printStackTrace();83 } catch(IOException e) {84 e.printStackTrace();85 }86 //得到工作表

87 if (inputStream == null) {88 return;89 }90 //文件名

91 String url =file.getFileName();92 //文件的后缀名

93 String suffix = url.substring(url.lastIndexOf("."));94 //HSSFWorkbook book = null;

95 Workbook book=null;96 /*try {97 //如果是xlsx格式，则这一步会报错98 book = new HSSFWorkbook(inputStream);99 } catch (IOException e) {100 e.printStackTrace();101 }*/

102 try{103 if(".xls".equals(suffix)){104 //支持07版本以前的excel

105 book= newHSSFWorkbook(inputStream);106 }else if(".xlsx".equals(suffix)){107 //支持07版本以后的excel

108 book = newXSSFWorkbook(inputStream);109 }else{110 System.out.println("不支持的文件类型！");111 return;112 }113 } catch(IOException e) {114 e.printStackTrace();115 }116 if (book == null) {117 return;118 }119 //得到一共有几个sheet

120 int sheetnum =book.getNumberOfSheets();121

122 //sheet页循环

123 for (int i = 0; i < sheetnum; i++) {124 boolean ifbbexist = false;125 boolean ifzzexist = false;126 boolean ifbbzzexist = false;127 Sheet sheet = null;128 sheet =book.getSheetAt(i);129 if (sheet != null) {130 //获取sheet页的名称

131 String sheetName =sheet.getSheetName();132 //获取报表中的最后一行的行号，则总行数等于它加1

133 int allrow = sheet.getLastRowNum() + 1;134

135 String sheetzzbh = "";136 String[] sheetnmmc = new String[2];137 if(ifcheck) {138 //如果是安组织导入，将得到的sheet页名称分开，以便获取报表编号

139 String[] sheetNames = sheetName.split("\\.");140 String bbbh = sheetNames[0];141 for (int n = 0; n < bbbhs.length; n++) {142 if(bbbh.equals(bbbhs[n])) {143 ifbbexist = true;144 }145 }146 if(ifbbexist) {147 dynm =getDynm(bbbh);148 item.setFcsjZznm(zznm);149 item.setFcsjZzbh(zzbh);150 item.setFcsjZzmc(zzmc);151 }152 } else{153 //将得到的sheet页名称分开，以便获取报表编号

154 String[] sheetNames = sheetName.split("\\.");155 String bbbh = sheetNames[0];156 //将得到的sheet页名称分开，以便获取组织编号

157 sheetzzbh = sheetNames[1].substring(sheetNames[1].indexOf("(") + 1, sheetNames[1].indexOf(")"));158 //判断此sheet页的报表编号是否是选中的

159 for (int b = 0; b < bbbhs.length; b++) {160 if(bbbh.equals(bbbhs[b])) {161 ifbbexist = true;162 }163 }164 //判断此sheet页的组织编号是否是选中的

165 for (int z = 0; z < zzbhs.length; z++) {166 if(sheetzzbh.equals(zzbhs[z])) {167 ifzzexist = true;168 }169 }170 if (ifbbexist &&ifzzexist) {171 ifbbzzexist = true;172 dynm =getDynm(bbbh);173 sheetnmmc =getZznmmc(sheetzzbh);174 item.setFcsjZznm(sheetnmmc[0]);175 item.setFcsjZzbh(sheetzzbh);176 item.setFcsjZzmc(sheetnmmc[1]);177 }178 }179 //1.(按组织导入)如果sheet页中的报表编号跟前台选中的编号相等，才能导入180 //2.(不按组织导入)sheet页中的报表编号和组织编号跟前台选中的编号相等，才能导入

181 if ((ifbbexist && ifcheck) ||ifbbzzexist) {182 //获取该报表是否是两栏表头的表格

183 List zhcs =getIfzh(dynm);184 String ifzh = zhcs.get(0);185 String kzhs = zhcs.get(1);186 int kzh =Integer.parseInt(kzhs);187 //获取该报表数据库里一共有几列

188 int tableCol =getTableCol(dynm);189 //获取内容行从哪一行开始

190 int rownum =getRownum(dynm);191 //内容行的行号(从1开始)

192 String mxnm =getMxnm(dynm);193 DataSet dsc =getSjl(dynm);194 DataSet dsr =getXxl(dynm);195 int xxls =getXxls(dynm);196 int hzbbStat =getStat(dynm, item);197 //编制行里的行记录

198 List> recordsList =getRecList(dynm,mxnm,item);199

200 int footerRow =getFooternum(dynm);201

202 //两栏表

203 if(ifzh.equals("1")){204 int rowXh = 1;205 for (int j = rownum; j < rownum+kzh-1; j++) {206 int firstLan =1;207 //cell单元格的值

208 String value = "";209 Map dataMap = new HashMap();210 int col = 0;211 //得到j的那一行

212 Row rowi =sheet.getRow(j);213 //得到该行的所有列

214 Iterator cellTitle =rowi.cellIterator();215 //循环标题所有的列

216 while(cellTitle.hasNext()) {217 if(0

248 String value = "";249 Map dataMap = new HashMap();250 int col = 0;251 //得到j的那一行

252 Row rowi =sheet.getRow(j);253 //得到该行的所有列

254 Iterator cellTitle =rowi.cellIterator();255 //循环标题所有的列

256 while(cellTitle.hasNext()) {257 Cell cell =(Cell) cellTitle.next();258 if(tableColtableCol*2){276 break;277 }278 secontLan++;279 }280 Date date = newDate();281 item.setCreatedtime(date);282 item.setLastmodifiedtime(date);283 exceldao.importExcel(dynm, dataMap, item, rowXh,mxnm,recordsList,xxls,hzbbStat,dsc,dsr);284 rowXh++;285 }286 }else{287 int rowXh = 1;288 //正常表

289 for (int j = rownum; j < rownum+recordsList.size(); j++) {290 //cell单元格的值

291 String value = "";292 Map dataMap = new HashMap();293 int col = 0;294 //得到j的那一行

295 Row rowi =sheet.getRow(j);296 //得到该行的所有列

297 Iterator cellTitle =rowi.cellIterator();298 //循环标题所有的列

299 while(cellTitle.hasNext()) {300 Cell cell =(Cell) cellTitle.next();301 /*cell.getCellType()返回的类型：302 int CELL_TYPE_NUMERIC = 0;303 int CELL_TYPE_STRING = 1;304 int CELL_TYPE_FORMULA = 2;305 int CELL_TYPE_BLANK = 3;306 int CELL_TYPE_BOOLEAN = 4;307 int CELL_TYPE_ERROR = 5;*/

308 int type =cell.getCellType();309 switch(type) {310 case 0:311 Double dValue =(Double) cell.getNumericCellValue();312 value =dValue.toString();313 break;314 case 1:315 value =cell.getStringCellValue();316 break;317 case 3:318 value = "";319 break;320 }321 dataMap.put(col, value);322 col++;323 }324 Date date = newDate();325 item.setCreatedtime(date);326 item.setLastmodifiedtime(date);327 exceldao.importExcel(dynm, dataMap, item, rowXh,mxnm,recordsList,xxls,hzbbStat,dsc,dsr);328 /*exceldao.importExcel(dynm,value,item);*/

329 rowXh++;330 }331 }332 }333 }334 }335 }336

337 //获取报表的对应内码

338 publicString getDynm(String bbbh) {339 String txnm = (String) getParameter("txnm");340 returnexceldao.getDynm(txnm, bbbh);341 }342 publicString getMxnm(String dynm) {343 returnexceldao.getMxnm(dynm);344 }345

346 //获取报表是否两栏表头

347 public ListgetIfzh(String dynm){348 returnexceldao.getIfzh(dynm);349 }350 //获取报表一共有几列

351 public intgetTableCol(String dynm){352 returnexceldao.getTableCol(dynm);353 }354

355 //根据sheet的组织编号获取组织的内码和名称

356 publicString[] getZznmmc(String sheetzzbh) {357 String txnm = (String) getParameter("sheetzzbh");358 returnexceldao.getZznmmc(sheetzzbh);359 }360

361 //确定报表从第几行开始才是数据行(内容行)

362 public intgetRownum(String dynm) {363 returnexceldao.getRownum(dynm);364 }365 publicDataSet getSjl(String dynm) {366 returnexceldao.getSjl(dynm);367 }368 publicDataSet getXxl(String dynm) {369 returnexceldao.getXxl(dynm);370 }371 public intgetXxls(String dynm) {372 returnexceldao.getXxls(dynm);373 }374 public intgetStat(String dynm,ExcelDr item) {375 returnexceldao.getStat(dynm,item);376 }377

378 //确定报表从第几行开始才是数据行(内容行)

379 public intgetFooternum(String dynm) {380 returnexceldao.getFooternum(dynm);381 }382 public List>getRecList(String dynm,String mxnm,ExcelDr item) {383 returnexceldao.getRecList(dynm,mxnm,item);384 }385

386 /**

387 *@param

388 *@returnvoid 返回类型389 *@throws

390 * @Title: getCS391 * @Description: 获取当前用户所属组织的信息392 */

393 public voidgetCS() {394

395 String organ_id = GetBspInfo.getBspInfo().getCorporationOrganId();//组织ID

396

397 if (organ_id == null) {398 setReturn("organ_id", "%");399 } else{400 String organ_name = OrganFactory.getIOrganProvider().getOrganByOrganId(organ_id).getOrganName();//组织姓名

401 setReturn("organ_id", organ_id);402 }403 }404

405 public voidgetParentOrgans() {406 String organId = (String) getParameter("organId");407 List parentOrgans = new ArrayList();408 parentOrgans =exceldao.getParentOrgans(organId);409 setReturn("organList", parentOrgans);410 }411 public voidgetFaslnm(){412 ParameterSet pset =getParameterSet();413 String faslnm=exceldao.getFaslnm(pset);414 setReturn("faslnm", faslnm);415 }416 }