利用JXL根据Exel的某一列进行拆分Excel

最新推荐文章于 2021-02-17 05:25:24 发布

原创最新推荐文章于 2021-02-17 05:25:24 发布 · 508 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel

Excel 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用Java和jxl库批量复制并按条件修改Excel文件的方法。该方法可以针对不同工作表中的特定单元格进行内容更新，并创建新的Excel文件。此过程通过实例演示了如何设置单元格格式、读取原始文件数据以及处理多个目标文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

请不要在意类名

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.omg.CORBA.Any;
import org.omg.CORBA.Object;
import org.omg.CORBA.TypeCode;
import org.omg.CORBA.portable.InputStream;

import jxl.CellView;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.WritableWorkbookImpl;

public class POIExcelTransform {
private static final HashMap String = null;
public static String tt="E:/WORKHome/";
public POIExcelTransform() {
// TODO Auto-generated constructor stub
}

/*************
* 我的第一次尝试
*/
public void copyExcel(){
  String whole_path=tt;
  String wholeName="待确认工作量-";
  Map<String, Integer> nameMap = new HashMap<String, Integer>();
  Map<String, WritableWorkbook> WritableWorkbookMap = new HashMap<String, WritableWorkbook>();
  try {
   Workbook Workbook_sourc  = Workbook.getWorkbook(new File(tt+"确认1.xls"));
   Workbook Workbook_model  = Workbook.getWorkbook(new File(tt+"确认1_mode.xls"));
   File excelFile = new File(tt+"test11.xls");
   //OutputStream tOutputStream = new FileOutputStream(excelFile);

   Sheet tSheet =Workbook_sourc.getSheet(0);
   int i_row=2;
   int j_column=0;
   int name_i=18;
   String curString="";
   String nextString="";
   WritableCellFormat cf = new WritableCellFormat(NumberFormats.TEXT);
   cf.setBorder(Border.ALL, BorderLineStyle.THIN);
   cf.setWrap(true);

   //行循环
   while(true){
    curString =tSheet.getCell(name_i, i_row).getContents();
    nextString=tSheet.getCell(name_i, i_row+1).getContents();;
    if(curString==null||"".equals(curString)){
     break;
    }
    File toFile = new File(whole_path+wholeName+curString+".xls");
    WritableWorkbook workbook_comfirm;
    if(!toFile.exists()){
     //不存在
     WorkbookSettings settings = new WorkbookSettings();
     settings.setWriteAccess(null);
     workbook_comfirm = Workbook.createWorkbook(toFile,Workbook_model,settings);
     WritableSheet tttsheet = workbook_comfirm.getSheet(0);
     tttsheet.getSettings().setDefaultRowHeight(100);
     tttsheet.getSettings().setDefaultColumnWidth(10);;
     for(int i=0;i<19;++i){
      Label label = new Label(i, 2, tSheet.getCell(i, i_row).getContents());

      label.setCellFormat(cf);
      // 将定义好的单元格添加到工作表中
      tttsheet.addCell(label);

     }
     WritableWorkbookMap.put(curString, workbook_comfirm);
     nameMap.put(curString, 2);
     //workbook_comfirm.write();
    // workbook_comfirm.close();
     //workbook_comfirm=null;
     //workbook_comfirm.write();
    }else{


     //存在
     workbook_comfirm=WritableWorkbookMap.get(curString);
     Integer lastInt=nameMap.get(curString);
     nameMap.put(curString, lastInt+1);

     WritableSheet tttsheet = workbook_comfirm.getSheet(0);
     for(int i=0;i<19;++i){
      Label label = new Label(i, lastInt+1, tSheet.getCell(i, i_row).getContents());
      // 将定义好的单元格添加到工作表中
      label.setCellFormat(cf);
      tttsheet.addCell(label);

     }

     //workbook_comfirm.write();
//     workbook_comfirm.write();
//
//     workbook_comfirm.close();
//     workbook_comfirm=null;
    }
    i_row++;
//    if(!curString.equals(nextString)){
//     workbook_comfirm.write();
////
//     workbook_comfirm.close();
//     workbook_comfirm=null;
//     System.gc();
//    }
   }

   Collection<WritableWorkbook> coWritableWorkbook= WritableWorkbookMap.values();
   Iterator<WritableWorkbook> itWritableWorkbook=coWritableWorkbook.iterator();
   while(itWritableWorkbook.hasNext()){
    WritableWorkbook WritableWorkbookworkbook_comfirm =itWritableWorkbook.next();
    WritableWorkbookworkbook_comfirm.write();
    WritableWorkbookworkbook_comfirm.close();
    WritableWorkbookworkbook_comfirm=null;
    System.gc();
   }

   Workbook_sourc.close();

  } catch (Exception e) {
   e.printStackTrace();
  }

}
public static void main(String[] args){
  try{
   System.out.println("！！！！！！！！！！！！！！");
   new POIExcelTransform().copyExcel();
   System.out.println("~~~~~~");
  }catch(Exception e){
   e.printStackTrace();
  }


}
}