Java 读写Excel文件

约定不等于承诺〃 2022-06-02 10:59 276阅读 0赞

package com.lss.common.excel;
    import java.io.BufferedWriter;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileWriter;
    import java.io.IOException;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    /**
     * @param filePath 文件路径
     * @return 读出的Excel的内容
     */
    public class ReaderExcelGenerationText {
     public StringBuffer getTextFromExcel(String filePath) {
      StringBuffer buff = new StringBuffer();
      try {
       // 创建对Excel工作簿文件的引用
       HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));
       // 创建对工作表的引用。 wb.getNumberOfSheets();
       for (int numSheets = 0; numSheets < wb.getNumberOfSheets(); numSheets++) {
        if (null != wb.getSheetAt(numSheets)) {
         HSSFSheet aSheet = wb.getSheetAt(numSheets);// 获得一个sheet
         for (int rowNumOfSheet = 0; rowNumOfSheet <= aSheet .getLastRowNum(); rowNumOfSheet++) {
          if (null != aSheet.getRow(rowNumOfSheet)) {
           HSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 获得一个行 
           // 获取列数aRow.getLastCellNum()
           for (short cellNumOfRow = 0; cellNumOfRow <=aRow.getLastCellNum(); cellNumOfRow++) {
            if (null != aRow.getCell(cellNumOfRow)) {
             HSSFCell aCell = aRow.getCell(cellNumOfRow);// 获得列值
             switch (aCell.getCellType()) {
             case HSSFCell.CELL_TYPE_FORMULA:
              break;
             case HSSFCell.CELL_TYPE_NUMERIC:
              String values = aCell.getNumericCellValue()+"";
              if (cellNumOfRow < 13) {
               int index = values.indexOf(".");
               values = values.substring(0, index).toString();
               buff.append(values).append('\t');
               break;
              } else {
               buff.append(values).append('\t');
               break;
              }
             case HSSFCell.CELL_TYPE_STRING:
              String value = aCell.getStringCellValue().toString().replaceAll("\n", "");
              value = value.replaceAll("\t", "");
              if (value.trim().equals("")) {
               value = "null";
              }
              buff.append(value).append('\t');
              break;
             default:
              buff.append("null").append("\t");
              break;
             }
            }
           }
           buff.append('\n');
          }
         }
        }
       }
       System.out.println(buff.toString());
      } catch (FileNotFoundException e) {
       e.printStackTrace();
      } catch (IOException e) {
       e.printStackTrace();
      }
      return buff;
     }
     public void writerFile(StringBuffer sb, String path) {
      try {
       FileWriter fw = new FileWriter(path);
       BufferedWriter bw = new BufferedWriter(fw);
       String str[] = sb.toString().split("\n");
       for (int i = 1; i < str.length; i++) {
        bw.write(str[i]);
        bw.newLine();
       }
       bw.flush();
       fw.flush();
       bw.close();
       fw.close();
       System.out.println("文件写入成功");
      } catch (IOException e) {
       e.printStackTrace();
      }
     }
     public static void main(String arg[]) {
      String path = "D:\\Test\\百货新品申请lhh.xls";
      String textPath="D:\\Test\\2010-01-01.txt";
      ReaderExcelGenerationText generationText = new ReaderExcelGenerationText();
      StringBuffer sb = generationText.getTextFromExcel(path);
      generationText.writerFile(sb, textPath);
     }
    }
     
    package com.lss.common.excel;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.List;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    public class HSSFWriteExcel {
     public static void main(String[] args) {
      try {
       Shell shell = new Shell();
       shell.setShellIndex(0);
       shell.setShellName("Test");
       
       Row row = new Row();
       Cell cell1 = new Cell("java.lang.String", "value1");
       Cell cell2 = new Cell("java.lang.String", "value2");
       Cell cell3 = new Cell("java.lang.String", "value3");
       row.addCell(cell1).addCell(cell2).addCell(cell3);
       shell.addRow(row).addRow(row);
       new HSSFWriteExcel().writeShell(new String[] { "第一", "第二", "第一1",
         "第二2", "第第二" }, shell, "D:\\test\\text.xls");
      } catch (IOException e) {
       e.printStackTrace();
      }
     }
     public void writeShell(String[] shellNames, Shell shell, String outFile)
       throws IOException {
      writeShell(shellNames, shell, new FileOutputStream(outFile));
     }
     public void writeShell(String[] shellNames, Shell shell,
       FileOutputStream fos) throws IOException {
      HSSFWorkbook wb = new HSSFWorkbook();
      int i = 0;
      for (String shellName : shellNames) {
       HSSFSheet hSheet = wb.createSheet();
       wb.setSheetName(i, shellName, HSSFWorkbook.ENCODING_UTF_16);
       writerExcel(hSheet, shell);
       ++i;
      }
      wb.write(fos);
      fos.flush();
      fos.close();
     }
     private void writerExcel(HSSFSheet hSheet, Shell shell) {
      List<Row> rowList = shell.getRowList();
      for (int i = 0; i < rowList.size(); i++) {
       Row row = rowList.get(i);
       HSSFRow hssRow = hSheet.createRow(i);
       for (int j = 0; j < row.getCellList().size(); j++) {
        Cell cell = row.getCellList().get(j);
        HSSFCell hssfCell = hssRow.createCell((short) (j));
        hssfCell.setEncoding(HSSFCell.ENCODING_UTF_16);
        hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
        hssfCell.setCellValue(cell.getValue());
       }
      }
     }
    }