java中的接口是类吗
305
2023-07-31
Jexcel实现按一定规则分割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.RowsExceededEhttp://xception;
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("一共RfAdnO " + cols + " 列");
int counter = 0; //工作表行游标
int fileCounts = 1; //用来标识创建的excel文档数目
while(counter //得到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 //创建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 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 rowCells = sheet.getRow(counter); for(int k=0; 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程序设计有所帮助。
//得到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 //创建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 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 rowCells = sheet.getRow(counter); for(int k=0; 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程序设计有所帮助。
//创建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 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 rowCells = sheet.getRow(counter); for(int k=0; 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程序设计有所帮助。
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 rowCells = sheet.getRow(counter); for(int k=0; 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程序设计有所帮助。
rowCells = sheet.getRow(counter);
for(int k=0; 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程序设计有所帮助。
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程序设计有所帮助。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。
发表评论
暂时没有评论,来抢沙发吧~