Java 读写Excel文件

最新推荐文章于 2024-09-14 18:46:05 发布

kevin_Luan

最新推荐文章于 2024-09-14 18:46:05 发布

阅读量2k

点赞数

分类专栏： poi 文章标签： poi java excel

本文链接：https://blog.csdn.net/kevin_Luan/article/details/7903311

版权

poi 专栏收录该内容

1 篇文章 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());
   }
  }
 }
}