import org.apache.log4j.Logger; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.springframework.beans.factory.annotation.Autowired; import java.io.BufferedReader; import java.io.FileInputStream; import java.io.InputStreamReader; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Locale; import javax.annotation.Resource; public class DefaultExecuteFileService implements ExecuteFileService { @Resource private ConfigurationService configurationService; private String ip; private String user; private String pwd; private String port; private static final Logger LOG = Logger.getLogger(DefaultExecuteFileService.class); private final String titleRow[] = {"文本1", "文本2", "文本3", "文本4", "文本5", "文本6", "文本7", "文本8"}; private static final String DATE_FORMAT_TRXID = "yyyyMMddHHmmss"; @Override public Boolean performImport(String inDir, String excelFilePath,Date startDate) { final File folderToscan = new File(inDir); final File targetFiles = folderToscan.listFiles();
return launchImport(inDir, targetFiles, excelFilePath,startDate); } /** * Launch import. * * @param pathToScan the path to scan * @return true, if successful */ public boolean launchImport(final String pathToScan, final File[] files, String excelFilePath,Date startDate) { if (files == null || files.length == 0) { LOG.info("No files to import!"); return true; } int numberOFailure = 0; String version = new SimpleDateFormat("yyyyMMdd").format(new Date()); if(startDate!=null){ version = new SimpleDateFormat("yyyyMMdd").format(startDate); } //count for files int countFile = 0; File tmpFile = null; for (int i = 0; i < files.length; i++) { tmpFile = files[i]; if ((tmpFile != null) && tmpFile.exists() && tmpFile.isFile()&&tmpFile.getName().contains(version)&&files.length>=3) { countFile++; } } if (countFile == 0) { LOG.info("No files to import, please check weather the file exists!"); } else { try { LOG.info("Found " + files.length + " under scanPath(" + pathToScan + ")!"); final String fileTempName = SDF_DATE_TRXID.get().format(new Date()) + ".xls"; File excelFile = new File(fileTempName);// 创建excel文件对象 //each file for (final File filePath : files) { Boolean resultJob = this.processFile(filePath.getAbsolutePath(), excelFile,excelFilePath); if (resultJob == null) { LOG.error("Can't process this kind of files... "); numberOFailure++; } if (!resultJob) { numberOFailure++; } } } catch (final Exception e) { LOG.error(" Exception,Error import file : " + " due to : " + e.getMessage(), e); numberOFailure++; } finally { // FileUtils.deleteAllFiles(files); } } return (numberOFailure > 0) ? false : true; } /** * 执行文件 */ Boolean processFile(String filePath, File targetFile,String excelFilePath) { boolean importSuccess = true; BufferedReader br = null; int count = this.getCountLine(filePath); int currentLine = 0; FileInputStream fi = null; InputStreamReader ir = null; FileOutputStream out = null; try { final long costTimeStart3 = System.currentTimeMillis(); LOG.info("Step1 Start:Import "); fi = new FileInputStream(filePath); ir = new InputStreamReader(fi, "GBK"); br = new BufferedReader(ir); HSSFWorkbook workbook = this.createExcel(); String line; while ((line = br.readLine()) != null){ ++currentLine; importData(workbook, line, count, currentLine); } out = new FileOutputStream(targetFile); workbook.write(out); SftpClientUtil.sshSftpUpLoadFile(ip,user,pwd,Integer.valueOf(port),excelFilePath,targetFile); final long costTimeEnd3 = System.currentTimeMillis(); final long totalTimeCost3 = costTimeEnd3 - costTimeStart3; LOG.info("Import finished in " + totalTimeCost3 + "ms"); } catch (final IOException e) { importSuccess = false; LOG.error("Failed import " + e); } finally { if (br != null) { try { br.close(); } catch (IOException e) { e.printStackTrace(); LOG.error("Problem closing the BufferedReader for file :" + filePath + " !"); } } if (null != ir) { try { ir.close(); } catch (IOException e) { e.printStackTrace(); LOG.error("Problem closing the BufferedReader for file :" + filePath + " !"); } } if (null != fi) { try { fi.close(); } catch (IOException e) { e.printStackTrace(); LOG.error("Problem closing the BufferedReader for file :" + filePath + " !"); } } if (null != out) { try { out.close(); } catch (IOException e) { e.printStackTrace(); LOG.error("Problem closing the BufferedReader for file :" + filePath + " !"); } } } return importSuccess; } public HSSFWorkbook createExcel() { //创建workbook HSSFWorkbook workbook = new HSSFWorkbook(); //加入Worksheet(不加入sheet时生成的xls文件打开时会报错) Sheet sheet1 = workbook.createSheet("first"); try { //加入表头 Row row = workbook.getSheet("first").createRow(0); //创建第一行 for (int i = 0; i < titleRow.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(titleRow[i]); } } catch (Exception e) { e.printStackTrace(); } return workbook; } private int getCountLine(String filePath) { BufferedReader brCount = null; int count = 0; FileInputStream fi = null; InputStreamReader ir = null; try { fi = new FileInputStream(filePath); ir = new InputStreamReader(fi, "GBK"); brCount = new BufferedReader(ir); brCount = new BufferedReader(ir); while (brCount.readLine() != null) { ++count; } } catch (final IOException e) { LOG.error("Failed import " + e); } finally { if (brCount != null) { try { brCount.close(); } catch (IOException e) { e.printStackTrace(); LOG.error("Problem closing the BufferedReader for file :" + filePath + " !"); } } if (null != ir) { try { ir.close(); } catch (IOException e) { e.printStackTrace(); LOG.error("Problem closing the BufferedReader for file :" + filePath + " !"); } } if (null != fi) { try { fi.close(); } catch (IOException e) { e.printStackTrace(); LOG.error("Problem closing the BufferedReader for file :" + filePath + " !"); } } } return count; } private void importData(HSSFWorkbook workbook, String line, int count, int currentLine) { if (currentLine != 1 && count != currentLine) { String[] lineArray = line.split("\\|"); Row row = workbook.getSheet("first").createRow(currentLine-1); for (int i = 0; i < lineArray.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(lineArray[i]); } } } public static final ThreadLocal<SimpleDateFormat> SDF_DATE_TRXID = new ThreadLocal<SimpleDateFormat>() { @Override protected SimpleDateFormat initialValue() { return new SimpleDateFormat(DATE_FORMAT_TRXID, Locale.FRENCH); } }; public ConfigurationService getConfigurationService() { return configurationService; } public void setConfigurationService(ConfigurationService configurationService) { this.configurationService = configurationService; } public String getIp() { return ip; } public void setIp(String ip) { this.ip = ip; } public String getUser() { return user; } public void setUser(String user) { this.user = user; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } public String getPort() { return port; } public void setPort(String port) { this.port = port; } }
Java读取文件,并把文件转换成excel
最新推荐文章于 2024-09-05 17:29:21 发布