［打造自己的代码库］ExcelUtil:（POI）解析Excel Sheet 与 String[][][]互转

package test.excelUtl.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;


/**
 * 
 * @author JZZ
 *
 */
public class ExcelUtil{
	public static void testHSSF(File file) throws Exception{
//		BufferedInputStream in = new BufferedInputStream(new FileInputStream("/Users/mac/Desktop/exce_201606.xls"));
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
		
		// Excel -> String[][][]
		Workbook wb = WorkbookFactory.create(in);
		String[][][] sheetArray = analyzeSheet(wb.getSheetAt(0));
		System.out.println(Arrays.deepToString(sheetArray));
		
		// String[][][] -> Excel
		HSSFWorkbook wb2 = new HSSFWorkbook();
		addSheetArrayToWorkbook(wb2,sheetArray,"sheet1");
		
		String fileName = file.getPath();
		if(!fileName.matches(".*\\.xls")){
			throw new RuntimeException("不是 xls 文件");
		}
		fileName = fileName.replace(".xls", "_export.xls");
		FileOutputStream fileOut = new FileOutputStream(fileName);
		wb2.write(fileOut);
		fileOut.close();
		
		System.out.println("export OK");
	}
	
	/**
	 * 解析 Sheet 为 字符数组
	 * @param sheet
	 * @return String[row][col][2]
	 */
	public static String[][][] analyzeSheet(Sheet sheet){
		int[] sheetMeasure = measureSheet(sheet);
		String[][][] sheetArray = initSheetArray(sheet,sheetMeasure[0],sheetMeasure[1]);
		correctSheetArray(sheet,sheetArray);
		return sheetArray;
	}
	/**
	 * 最终数据保存到String[].. 中 ，这样前端和后端（用json）交互更容易，
	 * 
	 * 将 sheet 读取到 String[row][clo][cell]中，
	 * 1. measureSheet(sheet)获取 sheet 的行数 和列数
	 * 2. 根据 sheet 初始化生成 sheetArray 
	 * 3. 根据 sheet 对 sheetArray 进行合并单元格

	 * [cell]的维度为2
	 *  [0]：单元格的值
	 *  [1]：单元格合并信息（"0":基本单元格；"1"：左合并；"2"：上合并；"3"：左上合并；"9":合并单元格的数据）
	 * 单元格合并信息 eg：
	 * 		[	"0"	,	"0"	,	"0"	,	"0"	]
	 * --------------------------------------------
	 * 		[	"0"	,	"9"		"1"	,	"9"	] 
	 * -------------------------------		--------
	 * 		[	"0"	,	"0"	,	"0"	,	"2"	] 
	 * -------------------------------		--------
	 * 		[	"0"	,	"0"	,	"0"	,	"2"	] 
	 * -------------------------------		-------
	 * 		[	"0"	,	"9"		"1"	,	"2"	] 
	 * --------------				--		-------
	 * 		[	"0"	,	"2"		"3"	,	"2"	] 
	 * --------------				--		------
	 * 		[	"0"	,	"2"	,	"3"	,	"2"	] 
	 * --------------------------------------------
	 * 		[	"0"	,	"0"	,	"0"	,	"0"	] 
	 * --------------------------------------------
	 * 		[	"0"	,	"0"	,	"0"	,	"0"	] 
	 */

	/**
	 * 获取 sheet 页 元素的 行数 和 列数
	 * @param sheet
	 * @return int[]   int[0]：行数  int[1]:列数
	 */
	private static int[] measureSheet(Sheet sheet){
		int[] measure = new int[2];
		measure[0] = sheet.getLastRowNum()+1; // getLastRowNum 0-based; getLastCellNum 1-based;
		Iterator
   
   
    
     rowIt = sheet.iterator();
		while(rowIt.hasNext()){
			Row row = rowIt.next();
			if(row.getLastCellNum() > measure[1]){
				measure[1] = row.getLastCellNum();
			}
		}
		return measure;
	}
	/**
	 * 使用 sheet 初始化 sheetArray ，
	 * 未合并单元格 还需调用 correctSheetArray 矫正 sheetArray
	 * @param sheet
	 * @param rowNum
	 * @param colNum
	 * @return
	 */
	private static String[][][] initSheetArray(Sheet sheet,int rowNum,int colNum){
		String[][][] sheetArray = new String[rowNum][colNum][2];
		try{
		for(int rowIndex=0; rowIndex
    
    
     
      rowIt = sheet.iterator();
		while(rowIt.hasNext()){
			Row row = rowIt.next();
			Iterator
     
     
      
       cellIt= row.iterator();
			while(cellIt.hasNext()){
				Cell cell = cellIt.next();
				sheetArray[cell.getRowIndex()][cell.getColumnIndex()] = readCell(cell) ;
			}
		}
		return sheetArray;
	}
	/**
	 * 根据 sheet 对 sheetArray 进行合并单元格操作
	 * @param sheet
	 * @param sheetArray
	 * @return
	 */
	private static void correctSheetArray(Sheet sheet, String[][][] sheetArray){
		List
      
      
       
        MergedList = sheet.getMergedRegions();

		int firstColumn = 0;
		int lastColumn = 0;
		int firstRow = 0;
		int lastRow = 0;
		for(CellRangeAddress addr : MergedList){
			firstColumn = addr.getFirstColumn();
			lastColumn = addr.getLastColumn();
			firstRow = addr.getFirstRow();
			lastRow = addr.getLastRow();
			if(firstColumn==lastColumn && firstRow==lastRow)// 存在一个单元格的 MergedCell
				continue;
			correctSheetArray(sheetArray, firstRow, lastRow, firstColumn, lastColumn);
		}
		return;
	}
	/**
	 * 对 sheetArray 继续(int firstRow, int lastRow, int firstColumn, int lastColumn) 范围内的合并单元格
	 * @param sheetArray
	 * @param firstRow
	 * @param lastRow
	 * @param firstColumn
	 * @param lastColumn
	 */
	private static void correctSheetArray(String[][][] sheetArray, int firstRow,
			int lastRow, int firstColumn, int lastColumn) {
		for(int r=firstRow; r<=lastRow; r++){
			for(int c=firstColumn; c<=lastColumn; c++){
				if(r==firstRow && c==firstColumn){
					sheetArray[r][c][1] = "9";
				}else if(r==firstRow && c>firstColumn){
					sheetArray[r][c][1] = "1";
				}else if(r>firstRow && c==firstColumn){
					sheetArray[r][c][1] = "2";
				}else{
					sheetArray[r][c][1] = "3";
				}
			}
		}
		return;
	}
	/**
	 * 读取 Cell 到String[2]内，如过 cell 为 null  返回{,"0"}
	 * @param cell
	 * @return
	 */
	private static String[] readCell(Cell cell){
		if(cell == null){
			String[] celStr = {"","0"};
			return celStr;
		}
		String value;
		switch(cell.getCellType()){
		case Cell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if(DateUtil.isCellDateFormatted(cell)){
				Date date = cell.getDateCellValue();
				if(date != null){
					value = new SimpleDateFormat("yyyy-MM-dd").format(date);
					
				}else{
					value = "";
				}
			}else{
				value = new DecimalFormat("0").format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_FORMULA:
			// 导入时如果为工时生成的数据则无值 ？？？
			if(!cell.getStringCellValue().equals("")){
				value = cell.getStringCellValue();
			}else{
				value = cell.getNumericCellValue() + "";
			}
			break;
		case Cell.CELL_TYPE_ERROR:
			value = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = (cell.getBooleanCellValue() == true ? "Y" : "N");
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			value = "";
		}
		
		String[] celStr = new String[2];
		celStr[0] = value;
		celStr[1] = "0";
		
		return celStr;
	}
	/**
	 * 在 wb 中创建一个 sheet ，名字 为 sheetName  数据为 sheetArray
	 * @param wb
	 * @param sheetArray
	 * @param sheetName
	 */
	public static void addSheetArrayToWorkbook(HSSFWorkbook wb,
			String[][][] sheetArray, String sheetName) {
		Sheet sheet = wb.createSheet(sheetName);
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		for(int rowIndex = 0; rowIndex