Java對(duì)xls文件進(jìn)行讀寫操作示例代碼

單位名 金額
單位1 948.34
單位2 4324
單位5 324

final String wsFileDir = "H:/OtherWorkDocs/ws"; // 原始數(shù)據(jù)存放的目錄
final String resFilePath = "H:/OtherWorkDocs/output/jan_feb_mar_sum.xls";
RWExcel rwExcel = new RWExcel(); // 操作xls的實(shí)例
// 獲取所有的名稱并排序
TreeSet<String> nameSet = rwExcel.getNameSet(wsFileDir);
// 將名稱與下標(biāo)存入map中
HashMap<String, Integer> nameRowHashMap = rwExcel.getNameRowHashMap(nameSet);
File wsDir = new File(wsFileDir); // 源文件目錄
File[] sourceFiles = wsDir.listFiles();
// 存儲(chǔ)單位名稱與金額對(duì)應(yīng)的數(shù)據(jù)
List<HashMap<String, Float>> dataList = new ArrayList<>(10);
if (sourceFiles != null) {
 for (File sF : sourceFiles) {
  // 裝載數(shù)據(jù)
  dataList.add(rwExcel.getSourceData(sF.getAbsolutePath()));
 }
}
// 原始數(shù)據(jù)已經(jīng)全部讀出來，和名稱一次性全部寫入
rwExcel.writeAllToResFile(resFilePath, nameRowHashMap, dataList);
// 補(bǔ)充標(biāo)題欄的標(biāo)題
if (null != sourceFiles) {
 int col = 1; // 起始列的序號(hào)
 for (File f : sourceFiles) {
  String fileName = f.getName();
  String name = fileName.substring(0, fileName.length() - 4);
  rwExcel.updateContent(resFilePath, name, 0, col);
  col++;
 }
}

public class RWExcel {
 /**
  * 存儲(chǔ)名稱
  */
 private TreeSet<String> nameTreeSet = new TreeSet<>();
 /**
  * 名稱以及排列的下標(biāo)號(hào)
  */
 private HashMap<String, Integer> nameRowMap = new HashMap<>();
 public TreeSet<String> getNameSet(String wsPath) {
  try {
   File wsDir = new File(wsPath);
   if (wsDir.exists() && wsDir.isDirectory()) {
    println("工作目錄存在");
    File[] files = wsDir.listFiles();
    if (files != null && files.length > 0) {
     for (File cFile : files) {
      getNamesFromFile(cFile, this.nameTreeSet);
     }
    }
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  this.nameTreeSet.comparator();
  return this.nameTreeSet;
 }
 /**
  * 將名稱Set排序后存入HashMap
  * 下標(biāo)從1開始
  */
 public HashMap<String, Integer> getNameRowHashMap(TreeSet<String> nameSet) {
  nameSet.comparator();
  int index = 1;
  for (String name : nameSet) {
   this.nameRowMap.put(name, index);
   index++;
  }
  return this.nameRowMap;
 }
 /**
  * 所有數(shù)據(jù)存入表格
  */
 public void writeAllToResFile(String resFilePath, Map<String, Integer> nameMap, List<HashMap<String, Float>> dataList) {
  File resFile = new File(resFilePath);
  if (!resFile.exists()) {
   try {
    resFile.createNewFile();
   } catch (IOException e) {
    e.printStackTrace();
   }
  }
  if (resFile.exists()) {
   try {
    // 先寫名稱
    WritableWorkbook wwb = Workbook.createWorkbook(resFile);
    WritableSheet ws = wwb.createSheet("sum", 0);
    Label label = new Label(0, 0, "單位名稱");
    ws.addCell(label);
    for (Map.Entry<String, Integer> entry : nameMap.entrySet()) {
     Label nameLabel = new Label(0, entry.getValue(), entry.getKey());
     ws.addCell(nameLabel);
     for (int j = 0; j < dataList.size(); j++) {
      Number zeroCell = new Number(j + 1, entry.getValue(), 0);
      ws.addCell(zeroCell);
     }
    }
    for (int dataColumn = 0; dataColumn < dataList.size(); dataColumn++) {
     HashMap<String, Float> dataMap = dataList.get(dataColumn);
     // 遍歷這個(gè)map 將所有的數(shù)據(jù)對(duì)應(yīng)填入
     for (Map.Entry<String, Float> dataEntry : dataMap.entrySet()) {
      int row = nameRowMap.get(dataEntry.getKey());
      Number numberCell = new Number(dataColumn + 1, row, dataEntry.getValue());
      ws.addCell(numberCell);
     }
    }
    wwb.write();
    wwb.close();
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
 }
 private void getNamesFromFile(File inputFile, TreeSet<String> hashSet) throws IOException, BiffException {
  Workbook workbook;
  InputStream is = new FileInputStream(inputFile);
  workbook = Workbook.getWorkbook(is);
  Sheet sheet0 = workbook.getSheet(0);
  int columnSum = sheet0.getColumns(); // 總列數(shù)
  int rsRows = sheet0.getRows();  // 總行數(shù)
  // 從1下標(biāo)開始
  for (int i = 1; i < rsRows; i++) {
   Cell cell = sheet0.getCell(0, i);
   if (!isEmpty(cell.getContents())) {
    hashSet.add(cell.getContents());
   }
  }
  println("此文件行數(shù)減一 = " + (rsRows - 1) + " , 當(dāng)前獲取到的所有單位數(shù) " + hashSet.size());
 }
 /**
  * 從原始數(shù)據(jù)中讀取并匹配的存入結(jié)果文件中
  */
 private HashMap<String, Float> getSourceData(String source) {
  File sFile = new File(source);
  if (!sFile.exists()) {
   System.out.println("原始文件不存在 復(fù)制失敗!");
   return null;
  }
  // 讀取源文件中的所有數(shù)據(jù) <單位名稱, 數(shù)值>
  HashMap<String, Float> sourceHashMap = new HashMap<>();
  try {
   Workbook sourceWs = Workbook.getWorkbook(sFile);
   Sheet sSheet0 = sourceWs.getSheet(0);
   int sTotalRows = sSheet0.getRows();  // 總行數(shù)
   for (int i = 1; i < sTotalRows; i++) {
    Cell cellKey = sSheet0.getCell(0, i);
    Cell cellValue = sSheet0.getCell(1, i);
    if (!isEmpty(cellKey.getContents()) && !isEmpty(cellValue.getContents())) {
     sourceHashMap.put(cellKey.getContents(), Float.valueOf(cellValue.getContents()));
    }
   }
   println(source + " 讀取到的數(shù)據(jù)數(shù)量 = " + sourceHashMap.size());
  } catch (Exception e) {
   e.printStackTrace();
  }
  return sourceHashMap;
 }
 public void updateContent(String filePath, String input, int row, int column) {
  File file = new File(filePath);
  if (!file.exists()) {
   System.out.println(filePath + " does not exist!");
   return;
  }
  try {
   Workbook sourceWb = Workbook.getWorkbook(file);
   WritableWorkbook wwb = Workbook.createWorkbook(file, sourceWb);
   WritableSheet wSheet0 = wwb.getSheet(0);
   Label label = new Label(column, row, input);
   wSheet0.addCell(label);
   wwb.write();
   wwb.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
 public RWExcel() {
 }
 private static boolean isEmpty(String str) {
  if (null == str) {
   return true;
  }
  return str.isEmpty();
 }
 private static void println(String in) {
  System.out.println(in);
 }
}

單位名稱 1月總金額 2月總金額 3月總金額
單位1 0 59.29999924 948.3400269
單位10 0 0 494.2000122
單位11 0 0 11.19999981
單位12 0 0 1.25
單位15 49.36000061 0 0
單位2 0 0 4324
單位24 0 34 0
單位5 0 23123 324
單位6 0 161.2599945 0