欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Jexcel实现按一定规则分割excel文件的方法

程序员文章站 2024-03-04 18:03:17
本文实例讲述了jexcel实现按一定规则分割excel文件的方法。分享给大家供大家参考。具体如下: 现有一个excel文档,需要读取它并按照一定的规则,分割之,分割出来的...

本文实例讲述了jexcel实现按一定规则分割excel文件的方法。分享给大家供大家参考。具体如下:

现有一个excel文档,需要读取它并按照一定的规则,分割之,分割出来的每一段记录需要单独创建一个excel文档并写入其中,一定要保证单元格格式的一致性。

package edu.bjut.zhutong.excelparser;
import java.io.file;
import java.io.fileinputstream;
import java.io.filenotfoundexception;
import java.io.ioexception;
import java.io.inputstream;
import jxl.cell;
import jxl.celltype;
import jxl.sheet;
import jxl.workbook;
import jxl.format.alignment;
import jxl.format.border;
import jxl.format.borderlinestyle;
import jxl.format.colour;
import jxl.format.verticalalignment;
import jxl.read.biff.biffexception;
import jxl.write.label;
import jxl.write.writablecellformat;
import jxl.write.writablefont;
import jxl.write.writablesheet;
import jxl.write.writableworkbook;
import jxl.write.writeexception;
import jxl.write.biff.rowsexceededexception;
public class excelparser {
 public static void main(string[] args) {
  workbook wb = null;
  try {
   //获得了workbook对象之后,就可以通过它得到sheet(工作表)对象了
   inputstream is = new fileinputstream("c:/excel/excel.xls");
   wb = workbook.getworkbook(is);
   // 获得第一个工作表对象
   sheet sheet = wb.getsheet(0);
   //获得工作表的行数和列数
   int rows = sheet.getrows();
   int cols = sheet.getcolumns();
   system.out.println("一共 " + rows + " 行");
   system.out.println("一共 " + cols + " 列");
   int counter = 0; //工作表行游标
   int filecounts = 1; //用来标识创建的excel文档数目
   while(counter<rows-1) {
    //得到counter行的所有单元格
    cell[] rowcells = sheet.getrow(counter);
    cell cell0 = rowcells[0];
    //判断单元格内容的类型
    if(cell0.gettype() == celltype.label) {
     system.out.println("正在解析第 " + filecounts + " 个文件....");
     //新建一个excel文档
     file file = new file("c:/excel/excel" + filecounts + ".xls");
     writableworkbook wwb = workbook.createworkbook(file);
     //设置excel文档的工作表
     writablesheet ws = wwb.createsheet("sheet1", 0);
     //第一行合并第0到第8列
     ws.mergecells(0, 0, 8, 0);
     //设置第7,8,9列的列宽
     ws.setcolumnview(6, 10);
     ws.setcolumnview(7, 45);
     ws.setcolumnview(8, 27);
     //向新建的表中写入数据,首先第一行先写入标题
     for(int k=0; k<rowcells.length; k++) {
      //创建writablefont对象用来格式化字体,这里是20号宋体,加粗
      writablefont wf = new writablefont(writablefont.createfont("宋体"), 20, writablefont.bold, false);
      //使用writablefont创建单元格格式化对象
      writablecellformat wcf = new writablecellformat(wf);
      //设置水平对齐方式
      wcf.setalignment(alignment.centre);
      //设置垂直对齐方式
      wcf.setverticalalignment(verticalalignment.centre);
      //设置边框和颜色
      wcf.setborder(border.all, borderlinestyle.thin, colour.black);
      cell cell = rowcells[k];
      label label = new label(k,0,cell.getcontents(),wcf);
      //添加单元格到表中
      ws.addcell(label);
      //设置第一行的行高
      ws.setrowview(0, 30*20, false);
     }
     //向新建的表中写入数据,第二行写入表头
     for(int c=0; c<cols; c++) {
      string colcon = sheet.getcell(c, 1).getcontents();
      writablefont wf = new writablefont(writablefont.createfont("宋体"), 12, writablefont.bold, false);
      writablecellformat wcf = new writablecellformat(wf);
      wcf.setalignment(alignment.centre);
      wcf.setverticalalignment(verticalalignment.centre);
      wcf.setborder(border.all, borderlinestyle.thin, colour.black);
      ws.addcell(new label(c,1,colcon,wcf));
      ws.setrowview(1, 18*20, false);
     }
     int rowcounts = 1; //用来遍历50
     counter++; //将游标移动到下一行
     if(counter == 1) //如果游标到了第二行 ,就自动把游标移动到第三行,第二行不需要处理
      counter = 2;
     int rowindex = 2; //每篇excel文档的游标
     rowcells = sheet.getrow(counter);
     cell0 = rowcells[0];
     while(cell0.gettype() == celltype.number && counter<rows-1) {
      rowcells = sheet.getrow(counter);
      for(int k=0; k<rowcells.length; k++) {
       writablefont wf = new writablefont(writablefont.createfont("宋体"), 12, writablefont.no_bold, false);
       writablecellformat wcf = new writablecellformat(wf);
       wcf.setalignment(alignment.centre);
       wcf.setverticalalignment(verticalalignment.centre);
       wcf.setborder(border.all, borderlinestyle.thin, colour.black);
       label label = new label(k,rowindex,rowcells[k].getcontents(),wcf);
       ws.addcell(label);
      }
      //用来处理备注列的边框
      {
       writablefont wf = new writablefont(writablefont.createfont("宋体"), 12, writablefont.no_bold, false);
       writablecellformat wcf = new writablecellformat(wf);
       wcf.setalignment(alignment.centre);
       wcf.setverticalalignment(verticalalignment.centre);
       wcf.setborder(border.all, borderlinestyle.thin, colour.black);
       label label = new label(8,rowindex,"",wcf);
       ws.addcell(label);
      }
      ws.setrowview(rowindex, 18*20, false);
      rowindex++;
      counter++;
      cell0 = sheet.getrow(counter)[0];
     }
     wwb.write();
     wwb.close();
     filecounts++;
    }
   }
   system.out.println("程序执行结束....");
  } catch (filenotfoundexception e) {
   e.printstacktrace();
  } catch (biffexception e) {
   e.printstacktrace();
  } catch (ioexception e) {
   e.printstacktrace();
  } catch (rowsexceededexception e) {
   e.printstacktrace();
  } catch (writeexception e) {
   e.printstacktrace();
  } finally {
   wb.close(); //关闭workbook对象
  }
 }
}

希望本文所述对大家的java程序设计有所帮助。