java 读取excel，写入excel(只限于xlsx格式)

首先建立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("[e-mail]") || account.contains("[e-mail]")){
			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