Source/Client/PLTClientBase/src/com/vci/client/common/excel/ExcelDocumentUtils.java

@@ -8,14 +8,11 @@
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
@@ -29,22 +26,22 @@

public class ExcelDocumentUtils {
   /**
    *写回到excel文档
    * 写回到excel文档
    * 
    * @param sheetDataSet
    *            <P>
    *           数据集合
    *                     <P>
    *                     数据集合
    * @param path
    *            <P>
    *            excel文档的路径
    *                     <P>
    *                     excel文档的路径
    * @param name
    *            <P>
    *            excel文档的名称
    *                     <P>
    *                     excel文档的名称
    * @throws VCIError
    * @throws IOException 
    * */
   public static void writeExcelDocument(String path, String name,
         List<SheetDataSet> sheetDataSet) throws VCIError, IOException {
    * @throws IOException
    */
   public static void writeExcelDocument(String path, String name, List<SheetDataSet> sheetDataSet)
         throws VCIError, IOException {
      // 打开工作簿
      Workbook workBook = makeWorkbook(name);
      // sheet
@@ -64,25 +61,24 @@
               row = sheet.createRow(rowIndex);
            }
            String[] rowData = rowDataSet.get(rowIndex);
            //row
            // row
            for (int columnIndex = 0; columnIndex < rowData.length; columnIndex++) {
               // cell
               Cell cell = row.getCell(columnIndex);
               if (cell == null) {
                  cell = row.createCell(columnIndex);
               }
               //赋ֵ
               // 赋ֵ
               cell.setCellValue(rowData[columnIndex]);
            }
         }
      }// end
      } // end
      FileOutputStream fileOutputStream = makeFileOutputStream(path, name);
      workBook.write(fileOutputStream);
      fileOutputStream.close();
   }
	
   public static void writeExcelDocument(String path, String name,
         SheetDataSet dataSet) throws VCIError, IOException {

   public static void writeExcelDocument(String path, String name, SheetDataSet dataSet) throws VCIError, IOException {
      // 打开工作簿
      Workbook workBook = makeWorkbook(name);
      // sheet
@@ -101,14 +97,14 @@
            row = sheet.createRow(rowIndex);
         }
         String[] rowData = rowDataSet.get(rowIndex);
         //row
         // row
         for (int columnIndex = 0; columnIndex < rowData.length; columnIndex++) {
            // cell
            Cell cell = row.getCell(columnIndex);
            if (cell == null) {
               cell = row.createCell(columnIndex);
            }
            //赋ֵ
            // 赋ֵ
            cell.setCellValue(rowData[columnIndex]);
         }
      }
@@ -117,9 +113,9 @@
      workBook.write(fileOutputStream);
      fileOutputStream.close();
   }
	
	
   public static List<SheetDataSet> readExcelDocument(String name,InputStream fileInputStream) throws VCIError, IOException {

   public static List<SheetDataSet> readExcelDocument(String name, InputStream fileInputStream)
         throws VCIError, IOException {
      return readExcelDocument(name, fileInputStream, false);
   }

@@ -127,28 +123,27 @@
    * 读取excel文档的数据
    * 
    * @param FileInputStream
    *            <P>
    *            excel文档
    *                        <P>
    *                        excel文档
    * @param name
    *            <P>
    *            excel文档的名称
    * @throws IOException 
    * */
   public static List<SheetDataSet> readExcelDocument(String name,InputStream fileInputStream, boolean isDelNullRow)
    *                        <P>
    *                        excel文档的名称
    * @throws IOException
    */
   public static List<SheetDataSet> readExcelDocument(String name, InputStream fileInputStream, boolean isDelNullRow)
         throws VCIError, IOException {
      InputStream bufferInputStream = getBufferInputStream(fileInputStream);
      // 打开工作簿
      Workbook workBook = getWorkbook(fileInputStream, bufferInputStream,
            name);
      Workbook workBook = getWorkbook(fileInputStream, bufferInputStream, name);
      // row
      int maxRow=0;
      int maxRow = 0;
      List<SheetDataSet> dataSet = new ArrayList<SheetDataSet>();
      for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) {
         Sheet sheet = workBook.getSheetAt(sheetIndex);
         SheetDataSet sheetDataSet = new SheetDataSet();
         sheetDataSet.setSheet(sheet.getSheetName());
         dataSet.add(sheetDataSet);
         //遍历
         // 遍历
         for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row rowDataSet = sheet.getRow(rowIndex);

@@ -160,16 +155,15 @@
               continue;
            }
            int currentcolumnSize = rowDataSet.getLastCellNum();
            if(maxRow<currentcolumnSize){
               maxRow=currentcolumnSize;
            if (maxRow < currentcolumnSize) {
               maxRow = currentcolumnSize;
            }
            // 
            //
            String[] values = new String[maxRow];
            Arrays.fill(values, "");
            boolean hasValue = false;
            //遍历
            for (int columnIndex = 0; columnIndex < rowDataSet
                  .getLastCellNum(); columnIndex++) {
            // 遍历
            for (int columnIndex = 0; columnIndex < rowDataSet.getLastCellNum(); columnIndex++) {
               String value = getCellValue(columnIndex, rowDataSet);
               values[columnIndex] = rightTrim(value);
               hasValue = true;
@@ -183,34 +177,31 @@
      return dataSet;
   }

	
   /**
    * 读取excel文档的数据
    * 
    * @param path
    *            <P>
    *            excel文档的路径
    *             <P>
    *             excel文档的路径
    * @param name
    *            <P>
    *            excel文档的名称
    * @throws IOException 
    * */
   public static List<SheetDataSet> readExcelDocument(String path, String name)
         throws VCIError, IOException {
    *             <P>
    *             excel文档的名称
    * @throws IOException
    */
   public static List<SheetDataSet> readExcelDocument(String path, String name) throws VCIError, IOException {
      FileInputStream fileInputStream = getFileInputStream(path, name);
      InputStream bufferInputStream = getBufferInputStream(fileInputStream);
      // 打开工作簿
      Workbook workBook = getWorkbook(fileInputStream, bufferInputStream,
            name);
      Workbook workBook = getWorkbook(fileInputStream, bufferInputStream, name);
      // row
      int maxRow=0;
      int maxRow = 0;
      List<SheetDataSet> dataSet = new ArrayList<SheetDataSet>();
      for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) {
         Sheet sheet = workBook.getSheetAt(sheetIndex);
         SheetDataSet sheetDataSet = new SheetDataSet();
         sheetDataSet.setSheet(sheet.getSheetName());
         dataSet.add(sheetDataSet);
         //遍历
         // 遍历
         for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row rowDataSet = sheet.getRow(rowIndex);

@@ -219,16 +210,15 @@
               continue;
            }
            int currentcolumnSize = rowDataSet.getLastCellNum();
            // 
            if(maxRow<currentcolumnSize){
               maxRow=currentcolumnSize;
            //
            if (maxRow < currentcolumnSize) {
               maxRow = currentcolumnSize;
            }
            String[] values = new String[maxRow];
            Arrays.fill(values, "");
            boolean hasValue = false;
            //遍历
            for (int columnIndex = 0; columnIndex < rowDataSet
                  .getLastCellNum(); columnIndex++) {
            // 遍历
            for (int columnIndex = 0; columnIndex < rowDataSet.getLastCellNum(); columnIndex++) {
               String value = getCellValue(columnIndex, rowDataSet);
               if (columnIndex == 0 && value.trim().equals("")) {
                  break;
@@ -245,7 +235,6 @@
      return dataSet;
   }

	
   private static Workbook makeWorkbook(String name) throws VCIError {

      // 创建
@@ -259,58 +248,53 @@

   }

   private static FileOutputStream makeFileOutputStream(String path,
         String name) throws VCIError, FileNotFoundException {
      FileOutputStream fileOutputStream = new FileOutputStream(path + "\\"
            + name);
   private static FileOutputStream makeFileOutputStream(String path, String name)
         throws VCIError, FileNotFoundException {
      FileOutputStream fileOutputStream = new FileOutputStream(path + "\\" + name);
      return fileOutputStream;
   }

   private static FileInputStream getFileInputStream(String path, String name)
         throws VCIError, FileNotFoundException {
   private static FileInputStream getFileInputStream(String path, String name) throws VCIError, FileNotFoundException {
      File xlsFile = new File(path + "\\" + name);
      FileInputStream fileInputStream = new FileInputStream(xlsFile);
      return fileInputStream;
   }

   private static BufferedInputStream getBufferInputStream(
         InputStream fileInputStream) throws VCIError {
      BufferedInputStream bufferInputStream = new BufferedInputStream(
            fileInputStream);
   private static BufferedInputStream getBufferInputStream(InputStream fileInputStream) throws VCIError {
      BufferedInputStream bufferInputStream = new BufferedInputStream(fileInputStream);
      return bufferInputStream;
   }

   /**
    * 读取单元格的值
    * */
    */
   private static String getCellValue(int column, Row row) {
      return getCellValue(row.getCell(column));
   }
	
	
   public static String getCellValue(Cell cell){

   public static String getCellValue(Cell cell) {
      String cellValueString = "";
      if(cell == null) return cellValueString;
      if (cell == null)
         return cellValueString;
      int cellType = cell.getCellType();
      SimpleDateFormat sdfDateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
      // add by xchao 2012.11.22 处理日期类型和Cell
      if(cellType == Cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)){
      if (cellType == Cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)) {
         Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
         cellValueString = sdfDateTime.format(date);
      }
      else if(cellType == Cell.CELL_TYPE_BLANK){
      } else if (cellType == Cell.CELL_TYPE_BLANK) {
         cellValueString = "";
      } else if(cellType == Cell.CELL_TYPE_BOOLEAN){
      } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
         cellValueString = cell.getBooleanCellValue() ? "1" : "0";
      } else if(cellType == Cell.CELL_TYPE_ERROR){
      } else if (cellType == Cell.CELL_TYPE_ERROR) {
         cellValueString = "";
      } else if(cellType == Cell.CELL_TYPE_FORMULA){
         try{
      } else if (cellType == Cell.CELL_TYPE_FORMULA) {
         try {
            cellValueString = String.valueOf(cell.getNumericCellValue());
         } catch (Exception e) {
            cellValueString = cell.getRichStringCellValue().getString();
         }
      } else if(cellType == Cell.CELL_TYPE_NUMERIC){
      } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
//         String[] dataFormatStrings = cell.getCellStyle().getDataFormatString().replace("_", "").trim().split("\\.");
//         int maxFracDigits = 0;
//         if(dataFormatStrings.length == 2){
@@ -320,23 +304,23 @@
//         } else {
//            cellValueString = cell.toString();
//         }
         //cell.setCellType(Cell.CELL_TYPE_STRING);
         //cellValueString = cell.getStringCellValue();
         // cell.setCellType(Cell.CELL_TYPE_STRING);
         // cellValueString = cell.getStringCellValue();
         DecimalFormat df = new DecimalFormat("#.#########");
         cellValueString = df.format(cell.getNumericCellValue());
      } else if(cellType == Cell.CELL_TYPE_STRING){
      } else if (cellType == Cell.CELL_TYPE_STRING) {
         // 移除数据列中前可在存在的"'"
         String val = cell.getStringCellValue();
         while(val.startsWith("'")){
         while (val.startsWith("'")) {
            val = val.substring(1);
         }
         cellValueString = val;
      } 
      }
      return cellValueString;
   }

   private static Workbook getWorkbook(InputStream fileInputStream,
         InputStream bufferInputStream, String name) throws VCIError, IOException {
   private static Workbook getWorkbook(InputStream fileInputStream, InputStream bufferInputStream, String name)
         throws VCIError, IOException {

      // 打开工作簿
      Workbook workBook = null;
@@ -357,7 +341,7 @@

   /**
    * ȥ���ַ��ұߵĿո�
    * */
    */
   private static String rightTrim(String value) {
      if (value == null) {
         return "";
@@ -372,17 +356,14 @@
      return value.substring(0, length);
   }


   public static void main(String args[]) throws IOException {
      try {
         List<SheetDataSet> sheetDataSet = readExcelDocument("D:\\",
               "a.xlsx");
         List<SheetDataSet> sheetDataSet = readExcelDocument("D:\\", "a.xlsx");
         writeExcelDocument("D:\\", "b.xlsx", sheetDataSet);
      } catch (VCIError e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
   }
	
	

}