首先建立maven 工程,pom.xml的内容如下:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>Excel</groupId> <artifactId>Excel</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>Excel</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties> <dependencies>
/*apache 的poi包主要是处理excel的*/ <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.11-beta1</version> </dependency> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.11-beta1</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> </dependencies>
/*由于要导出jar包并且发布线上,所以一下配置主要是导出jar的设置*/ <build> <plugins> <plugin> <artifactId>maven-assembly-plugin</artifactId> <configuration> <appendAssemblyId>false</appendAssemblyId> <descriptorRefs> <descriptorRef>jar-with-dependencies</descriptorRef> </descriptorRefs> <archive> <manifest> <mainClass>Excel.ExcelOperate</mainClass>//main入口类 </manifest> </archive> </configuration> <executions> <execution> <id>make-assembly</id> <phase>package</phase> <goals> <goal>assembly</goal> </goals> </execution> </executions> </plugin> </plugins> </build> </project>
工程目录如下:
主要的处理类是ExcelOperate,里面有读取xlsx格式的excel和写入excel。
package Excel; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Properties; import java.util.Set; import java.util.logging.FileHandler; import java.util.logging.Logger; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelOperate { protected final static Logger logger = Logger.getLogger("log"); protected static FileHandler fileHandler = null; private static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:ss"); private static DecimalFormat decimalFormat = new DecimalFormat("0"); private static Map<String, String> mapRel = new HashMap<String, String>(); /** * hotel_orders key 酒店ID value map(Key 支付人账号 value List(订单)) */ private static Map<String, Map<String, Set<Order>>> hotel_orders_map = new HashMap<String, Map<String, Set<Order>>>(); /** * orders key 酒店ID value 支付数 */ private static Map<String, Integer> hotel_map = new HashMap<String, Integer>(); private static int orderCount = 0; public static void main(String[] args) throws Exception { Properties prop = new Properties(); final String CONFIGNAME = "/env/PathConfig.properties"; prop.load(ExcelOperate.class.getResourceAsStream(CONFIGNAME)); String path = prop.getProperty("path"); final String logPath =path+ "myLog"; File file = new File(path+"excel/source.xlsx"); getData(file, 5,logPath); List<Export> exportList = method(hotel_orders_map, hotel_map); Integer countColumnNum = Integer.parseInt(prop.getProperty("columCount")); String hotelId = prop.getProperty("hotelId"); String hotelName = prop.getProperty("hotelName"); String chargeCount = prop.getProperty("chargeCount"); String paymentCount = prop.getProperty("paymentCount"); String averageCount = prop.getProperty("averageCount"); String payments = prop.getProperty("payments"); String[] options = {hotelId,hotelName,chargeCount,paymentCount,averageCount,payments}; writeXls(exportList,countColumnNum,options, path+"excel/result.xlsx"); } /*读取excel*/ public static void getData(File file, int ignoreRows,String logPath) throws FileNotFoundException, IOException, InvalidFormatException { XSSFWorkbook wb = new XSSFWorkbook(file); for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { XSSFSheet st = wb.getSheetAt(sheetIndex); for (int rowIndex = ignoreRows; rowIndex < st.getLastRowNum(); rowIndex++) { XSSFRow row = st.getRow(rowIndex); logger.info("读取excel的行数" + rowIndex); if (row == null) { logger.info("row is null."); continue; } if(row.getCell(3)==null || row.getCell(3).getStringCellValue() == null){ logger.info("cell 3 is null."); break; } else { if (row.getCell(3).getStringCellValue().contains("火车票")) { continue; } } if(row.getCell(5)==null || row.getCell(5).getStringCellValue() == null){ logger.info("cell 5 is null."); break; } else { if (row.getCell(5).getStringCellValue().startsWith("支付宝")) { continue; } } String[] values = new String[row.getLastCellNum()+1]; boolean hasValue = false; for (int columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) { String value = ""; XSSFCell cell = row.getCell(columnIndex); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = dateFormat.format(date); } } else { value = decimalFormat.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_FORMULA: // 导入时如果为公式生成的数据则无值 if (!cell.getStringCellValue().equals("")) { value = cell.getStringCellValue(); } break; case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: break; case HSSFCell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue() ? "Y" :"N"; break; } } values[columnIndex] = value.trim(); hasValue = true; } if(allIsEmpty(values)){ break; } if (hasValue) { function(values,rowIndex,logPath); } } } wb.close(); } private static boolean allIsEmpty(String[] values){ for(String str : values){ if(!str.isEmpty()){ return false; } } return true; } @SuppressWarnings("resource") public static void writeXls(List<Export> exportList,Integer countColumnNum,String[] options, String path) throws Exception { XSSFWorkbook book = new XSSFWorkbook(); CreationHelper createHelper = book.getCreationHelper(); XSSFCellStyle style = book.createCellStyle(); style.setWrapText(true); XSSFSheet sheet = book.createSheet("orderSheet"); sheet.setColumnWidth(3, 13000); sheet.setDefaultColumnWidth(20); XSSFRow firstRow = sheet.createRow(0); XSSFCell[] firstCells = new XSSFCell[countColumnNum]; for (int j = 0; j < options.length; j++) { firstCells[j] = firstRow.createCell(j); firstCells[j].setCellValue(new XSSFRichTextString(options[j])); } getExport(sheet,style,createHelper,exportList); File file = new File(path); file.getParentFile().mkdirs(); OutputStream os = new FileOutputStream(file); book.write(os); os.close(); } public static void getExport(XSSFSheet sheet,XSSFCellStyle style,CreationHelper createHelper,List<Export> exportList){ int count = hotel_orders_map.size(); for (int i = 0; i < count; i++) { XSSFRow row = sheet.createRow(i + 1); Export export = exportList.get(i); XSSFCell hotelId = row.createCell(0); XSSFCell hotelName = row.createCell(1); XSSFCell chargeCount = row.createCell(2); XSSFCell paymentCount = row.createCell(3); XSSFCell averageCount = row.createCell(4); XSSFCell payments = row.createCell(5); hotelId.setCellValue(export.getHotelId()); hotelName.setCellValue(export.getHotelName()); chargeCount.setCellValue(export.getChargeCount()); paymentCount.setCellValue(export.getPaymentCount()); averageCount.setCellValue(export.getAverageCount()); payments.setCellStyle(style); String test = createHelper.createRichTextString( export.getDisplayPay()).toString(); if(test.length() > 32767){ logger.info("太长!:"+export.getPaymentMap()); logger.info("太长!:"+test); throw new RuntimeException("太长!"); } payments.setCellValue(test); logger.info("写入excel开始,行数是" + i + 1); } } public static void function(String[] values,int rowIndex,String logPath) throws SecurityException, IOException { if (values.length < 3) { return; } StringBuilder sb = new StringBuilder(); for (String str : values) { sb.append(str).append(" "); } logger.info("读取一行excel,数据:" + rowIndex + " "+ sb); String account = values[5]; String time = values[4]; String payOrderId = values[2]; String orderId = findOrderIdFromPayOrderId(payOrderId); String[] hotelInfo = findHotelIdFromOrderId(orderId); if(hotelInfo==null){ return; } String hotelId = hotelInfo[0]; String hotelName = hotelInfo[1]; if(hotelId == null && hotelName == null){ return; } Order order = new Order(account,time,orderId); mapRel.put(hotelId, hotelName); if (!hotel_map.containsKey(hotelId)) { hotel_map.put(hotelId, 1); } else { hotel_map.put(hotelId,hotel_map.get(hotelId) + 1); } if (!hotel_orders_map.containsKey(hotelId)) { hotel_orders_map.put(hotelId, new HashMap<String, Set<Order>>()); } if(hotel_orders_map.get(hotelId).get(account) == null){ hotel_orders_map.get(hotelId).put(account, new HashSet<Order>()); } hotel_orders_map.get(hotelId).get(account).add(order); if(account.contains("871849780@qq.com") || account.contains("ljt19841151@163.com")){ if(fileHandler==null){ fileHandler = new FileHandler(logPath); logger.addHandler(fileHandler); } logger.info("账号:"+account+"酒店ID:"+hotelId+"订单ID:"+orderId+"行号:"+rowIndex); } } @SuppressWarnings("unused") private static String[] getHotelInfoTest(String newStr,String orderId){ String[] hotelInfo = null; if(!newStr.contains("火车") && newStr.contains("订单ID")){ int first = newStr.indexOf(":"); if(first == -1){ first = newStr.indexOf(":"); } int end = newStr.lastIndexOf(")"); if(end == -1){ end = newStr.lastIndexOf(")"); } if(first != -1 && end != -1){ orderId = newStr.substring(first+1,end).trim(); hotelInfo = findHotelIdFromOrderId(orderId); orderCount++; } return hotelInfo; } return null; } private static String[] findHotelIdFromOrderId(String orderId) { String[] hotelInfo = DatabaseConn.searchHotel(orderId); if(hotelInfo==null){ logger.info("没有对应酒店"); } return hotelInfo; } private static String findOrderIdFromPayOrderId(String payOrderId){ String orderId = DatabaseConn.searchOrderId(payOrderId); return orderId; } public static List<Export> method( Map<String, Map<String, Set<Order>>> hotelOrderMap, Map<String, Integer> hotelMap) { List<Export> exportList = new LinkedList<Export>(); for (Entry<String, Map<String, Set<Order>>> entry : hotelOrderMap .entrySet()) { Export export = new Export(); String hotelId = entry.getKey().trim(); export.setHotelId(hotelId); export.setPaymentCount(entry.getValue().size()); export.setHotelName(mapRel.get(hotelId)); export.setChargeCount(hotelMap.get(hotelId)); export.setAverageCount(export.getChargeCount()/ (double)export.getPaymentCount()); export.setDisplayPay(entry.getValue()); exportList.add(export); } return exportList; } public static String skipBlankTrim(String value){ StringBuilder sb = new StringBuilder(); String[] strs = value.split(" "); for(String str : strs){ sb.append(str); } return sb.toString().trim(); } }
工程建立起来后,直接到工程目录(pom.xml所在的目录),cmd输入mvn clean install 就可以在工程目录下target中找到导出的jar 包。
在服务器的 cd /home/work/hotel/java/bin/
jar 包运行脚本:./java -jar /home/tester/Excel-0.0.1-SNAPSHOT.jar Excel.ExcelOperate -Xms1800M -XX:MaxNewSize=256m -server
相关推荐
完美解决java读取excel内存溢出问题,希望可以帮到大家
java 读取Excel文件中的内容 java 读取Excel文件中的内容
开发调用Poi进行读取生成操作Excel的jar。含有:要引入的jar包有xmlbeans-2.6.0.jar, poi-3.9-20121203.jar, poi-excelant-3.9.jar, poi-ooxml-3.9.jar, poi-ooxml-schemas-3.9.jar, dom4j-1.6.1.jar。
java读取excel文件,兼容xlsx格式,可以用于07版本和10版本
java 导入Excel 文件,兼容Excel 2003(后缀名:xls)及 2007(后缀名:xlsx)的文件,同时还支持csv格式的文件
//C# 读取Excel文件、C#读取xls文件、C#读取xlsx文件、C#读取csv文件 //C# 将xls文件转换为DataTable、C#将xlsx文件转换为DataTable //C#将csv文件转换为DataTable //C#将txt文件转换为DataTable(列与列之间空格隔...
java读取excel 表格数据。 public static void main(String[] args) { String[][] content=POIExcelUtils.read("E:\\1.xlsx"); for(int i=0;i;i++) { if(content[i]!=null){ for(int j=0;j[i].length;j...
java使用poi读取xls格式和xlsx格式的excel文件
1、POI_EXCEL包下分别有两个类,一个是读取excel内容,一个是想excel写入内容 2、cn.itcast包下的所有包,是为了实现从excel写入到数据库中,和从数据库写入到excel中 注意事项: 1、需要导入maven工程 2、使用...
1、java解析读取excel文件中的数据,并写入数据库。 2、java读取数据库数据,并导出为excel文件。 3、README.md中有详细的操作步骤示例。 使用说明: 1. 先使用postman导入:other/excel相关.postman_collection....
该代码可以处理100万数据量的excel文件,xlsx文件数据量太大,用普通的读法会报内存溢出错误,所以用官网提供的方法,一条一条的读取大excel文件,本例子从这点出发,组装excel里读取的单条数据为list,在根据需求...
使用Java读取Excel文件的内容,主要是使用POI框架,可以读取最新的Excel文件xlsx后缀文件,即2007版本之后的。
本博需要的测试文件,
读取的excel文件位于项目目录下的 excel\0805.xlsx 使用IntelliJ IDEA开发此项目 使用MYSQL查看数据库 在MYSQL中运行项目db目录下的sql文件,创建目标数据库及表格结构 根据需求修改下面文件中的数据库端口、访问...
C# 编程实现Excel 2007表格的读取与写入实例 读写xlsx格式的文件
* excel 格式 定义 * 第一行 为 说明, * 第二行 为 字段说明 * 第三行 为 字段名字 * 第四行 为 前端字段类型 (我是后端我不用,所以不解析,) * 第五行 为 后端 字段类型,int string float * 从第6行...
java实现对Excel的读取,并保存到数据库中!代码简洁有注释!!!
java读取excel电子表格所需jar包,包括xls 格式的exel文件的读取和xlsx格式文件的读取
java读取excel的工具类型,可同时支持excel2003和2007版本
Java读取xls、CSV、xlsx文件所用到的jar包,包括:opencsv-2.3.jar、poi-3.7-20101029.jar、poi-ooxml-3.7-20101029.jar