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;
@@ -42,9 +39,9 @@
    *            excel文档的名称
    * @throws VCIError
    * @throws IOException 
    * */
   public static void writeExcelDocument(String path, String name,
         List<SheetDataSet> sheetDataSet) throws VCIError, IOException {
    */
   public static void writeExcelDocument(String path, String name, List<SheetDataSet> sheetDataSet)
         throws VCIError, IOException {
      // 打开工作簿
      Workbook workBook = makeWorkbook(name);
      // sheet
@@ -81,8 +78,7 @@
      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
@@ -118,8 +114,8 @@
      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);
   }

@@ -133,13 +129,12 @@
    *            <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;
      List<SheetDataSet> dataSet = new ArrayList<SheetDataSet>();
@@ -168,8 +163,7 @@
            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,7 +177,6 @@
      return dataSet;
   }

	
   /**
    * 读取excel文档的数据
    * 
@@ -194,14 +187,12 @@
    *            <P>
    *            excel文档的名称
    * @throws IOException 
    * */
   public static List<SheetDataSet> readExcelDocument(String path, String name)
         throws VCIError, 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;
      List<SheetDataSet> dataSet = new ArrayList<SheetDataSet>();
@@ -227,8 +218,7 @@
            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,46 +248,41 @@

   }

   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){
      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)){
         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){
         cellValueString = cell.getBooleanCellValue() ? "1" : "0";
@@ -335,8 +319,8 @@
      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();
      }
   }
	
   
}