jxl读写excel常见操作搜集整理.docx
《jxl读写excel常见操作搜集整理.docx》由会员分享,可在线阅读,更多相关《jxl读写excel常见操作搜集整理.docx(13页珍藏版)》请在冰豆网上搜索。
jxl读写excel常见操作搜集整理
jxl读写excel常见操作搜集整理
packagecommon.excelTool;
importjava.io.File;
importjava.io.FileOutputStream;
importjava.io.OutputStream;
importjava.util.ArrayList;
importjava.util.Date;
importjava.util.HashMap;
importjxl.Cell;
importjxl.CellType;
importjxl.DateCell;
importjxl.Range;
importjxl.Sheet;
importjxl.Workbook;
importjxl.WorkbookSettings;
importjxl.format.Alignment;
importjxl.format.Border;
importjxl.format.BorderLineStyle;
importjxl.format.CellFormat;
importjxl.format.Colour;
importjxl.format.UnderlineStyle;
importjxl.format.VerticalAlignment;
importjxl.write.Formula;
importjxl.write.Label;
importjxl.write.Number;
importjxl.write.NumberFormat;
importjxl.write.WritableCell;
importjxl.write.WritableCellFeatures;
importjxl.write.WritableCellFormat;
importjxl.write.WritableFont;
importjxl.write.WritableSheet;
importjxl.write.WritableWorkbook;
importjxl.write.WriteException;
importjxl.write.biff.RowsExceededException;
publicclassWriteExcelTest{
staticHashMapmap=newHashMap();
publicstaticvoidmain(String[]args){
try{
//copyDateFormat(newFile("c:
\\a.xls"),0,"c:
\\copyofa.xls");
//writeExcelUseFormat("c:
\\format.xls","test");
//buildNewFormTemplete(newFile("c:
/templete.xls"),newFile("c:
/buildNewFormTemplete.xls"));
//modifyDirectly1(newFile("c:
/templete.xls"));
//modifyDirectly2(newFile("c:
/templete.xls"));
copyDateAndFormat(newFile("c:
/a.xls"),0,"c:
/a2.xls");
}catch(Exceptione){
//TODO自动生成catch块
e.printStackTrace();
}
}
publicstaticvoidmodifyDirectly2(FileinputFile)throwsException{
Workbookw1=Workbook.getWorkbook(inputFile);
WritableWorkbookw2=Workbook.createWorkbook(inputFile,w1);
WritableSheetsheet=w2.getSheet(0);
WritableCellcell=null;
CellFormatcf=null;
//加粗
cell=sheet.getWritableCell(0,0);
WritableFontbold=newWritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.BOLD);
cf=newWritableCellFormat(bold);
cell.setCellFormat(cf);
//设置下划线
cell=sheet.getWritableCell(0,1);
WritableFontunderline=newWritableFont(WritableFont.ARIAL,
WritableFont.DEFAULT_POINT_SIZE,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE);
cf=newWritableCellFormat(underline);
cell.setCellFormat(cf);
//直截添加可以覆盖掉
setCellValueDirectly(sheet,sheet.getCell(0,2),newDouble(4),CellType.NUMBER);
w2.write();
w2.close();
}
publicstaticvoidmodifyDirectly1(Filefile){
try{
//Excel获得文件
Workbookwb=Workbook.getWorkbook(file);
//打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbookbook=Workbook.createWorkbook(file,
wb);
WritableSheetsheet0=book.getSheet(0);
sheet0.addCell(newLabel(0,1,"陈小稳"));
//添加一个工作表
WritableSheetsheet=book.createSheet("第二页",1);
sheet.addCell(newLabel(0,0,"第二页的测试数据"));
book.write();
book.close();
}catch(Exceptione){
System.out.println(e);
}
}
publicstaticvoidbuildNewFormTemplete(FileinputFile,FileoutputFile){
try{
//Excel获得文件
Workbookwb=Workbook.getWorkbook(inputFile);
//打开一个文件的副本,并且指定数据写回到原文件
WritableWorkbookbook=Workbook.createWorkbook(outputFile,
wb);
WritableSheetsheet0=book.getSheet(0);
sheet0.addCell(newLabel(0,1,"陈小稳"));
//添加一个工作表
WritableSheetsheet=book.createSheet("第二页",1);
sheet.addCell(newLabel(0,0,"第二页的测试数据"));
book.write();
book.close();
}catch(Exceptione){
System.out.println(e);
}
}
publicstaticvoidcopyDateAndFormat(FileinputFile,intinputFileSheetIndex,StringoutputFilePath)throwsException{
Workbookbook=null;
Cellcell=null;
//1.避免乱码的设置
WorkbookSettingssetting=newWorkbookSettings();
java.util.Localelocale=newjava.util.Locale("zh","CN");
setting.setLocale(locale);
setting.setEncoding("ISO-8859-1");
book=Workbook.getWorkbook(inputFile,setting);
SheetreadonlySheet=book.getSheet(inputFileSheetIndex);
OutputStreamos=newFileOutputStream(outputFilePath);//输出的Excel文件URL
WritableWorkbookwwb=Workbook.createWorkbook(os);//创建可写工作薄
WritableSheetwritableSheet=wwb.createSheet(readonlySheet.getName(),0);//创建可写工作表
//2.誊写不同数据格式的数据
for(introwIndex=0;rowIndexfor(intcolIndex=0;colIndexcell=readonlySheet.getCell(colIndex,rowIndex);
//A2B2为合并的单元格,A2有内容,B2为空
//if(colIndex==0&&rowIndex==1){
//System.out.println(colIndex+","+rowIndex+"type:
"+cell.getType()+":
"+cell.getContents());
//}
//【有各种设置格式】
if(cell.getType()==CellType.DATE||cell.getType()==CellType.DATE_FORMULA){
writableSheet.addCell(
newjxl.write.DateTime(
colIndex
rowIndex
((DateCell)cell).getDate(),
newjxl.write.WritableCellFormat(
cell.getCellFormat()
)
)
);
}elseif(cell.getType()==CellType.NUMBER||cell.getType()==CellType.NUMBER_FORMULA){
writableSheet.addCell(
newjxl.write.Number(
colIndex
rowIndex
((jxl.NumberCell)cell).getValue(),
newjxl.write.WritableCellFormat(
cell.getCellFormat()
)
)
);
}elseif(cell.getType()==CellType.EMPTY){
//空的以及合并单元格中第一列外的
//System.out.println("EMPTY:
"+cell.getContents());
//System.err.println("空单元格at"+colIndex+","+rowIndex+"content:
"+cell.getContents());
}elseif(cell.getType()==CellType.LABEL||cell.getType()==CellType.STRING_FORMULA){
writableSheet.addCell(
newLabel(
colIndex
rowIndex
cell.getContents()
newjxl.write.WritableCellFormat(
cell.getCellFormat()
)
)
);
}else{
System.err.println("其它单元格类型:
"+cell.getType()+"at"+colIndex+","+rowIndex+"content:
"+cell.getContents());
}
//if(cell.getType()==CellType.STRING_FORMULA){
//System.err.println(colIndex+","+rowIndex+":
"+cell.getContents()+"type:
"+cell.getType());
//}
}
}
//3.处理合并单元格的事情(复制合并单元格格式)
Range[]range=readonlySheet.getMergedCells();
for(inti=0;i//System.out.println("第"+i+"处合并的单元格:
"
//+",getTopLeft="+range[i].getTopLeft().getColumn()
//+","+range[i].getTopLeft().getRow()
//+",getBottomRight="+range[i].getBottomRight().getColumn()
//+","+range[i].getBottomRight().getRow()
//);
//topleftXIndex,topleftYIndex,bottomRightXIndex,bottomRightYIndex
writableSheet.mergeCells(
range[i].getTopLeft().getColumn(),
range[i].getTopLeft().getRow(),
range[i].getBottomRight().getColumn(),
range[i].getBottomRight().getRow()
);
}
//4.设置行列高宽
for(intcolIndex=0;colIndexwritableSheet.setColumnView(colIndex,readonlySheet.getColumnView(colIndex));
}
for(introwIndex=0;rowIndexwritableSheet.setRowView(rowIndex,readonlySheet.getRowView(rowIndex));
}
wwb.write();
wwb.close();
os.close();
}
publicstaticvoidwriteExcelUseFormat(StringoutputFilePath,StringoutputFileSheetName)throwsException{
OutputStreamos=newFileOutputStream(outputFilePath);//输出的Excel文件URL
WritableWorkbookwwb=Workbook.createWorkbook(os);//创建可写工作薄
WritableSheetsheet=wwb.createSheet(outputFileSheetName,0);//创建可写工作表
sheet.addCell(newLabel(0,0,"号码"));
sheet.addCell(newLabel(1,0,"有效期"));
//1.写入时间的数据格式
jxl.write.DateFormatdf=newjxl.write.DateFormat("yyyy-MM-dd");
jxl.write.WritableCellFormatwcfDF=newjxl.write.WritableCellFormat(df);
jxl.write.DateTimelabelDTF=newjxl.write.DateTime(1,1,newDate(),wcfDF);//自定义格式
sheet.addCell(labelDTF);
//2.字体样式
//WritableFont()方法里参数说明:
//这个方法算是一个容器,可以放进去好多属性
//第一个:
TIMES是字体大小,他写的是18
//第二个:
BOLD是判断是否为斜体,选择true时为斜体
//第三个:
ARIAL
//第四个:
UnderlineStyle.NO_UNDERLINE下划线
//第五个:
jxl.format.Colour.RED字体颜色是红色的
jxl.write.WritableFontwf=newjxl.write.WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
jxl.write.WritableCellFormatwcfF=newjxl.write.WritableCellFormat(wf);
wcfF.setWrap(true);//自动换行
wcfF.setAlignment(jxl.format.Alignment.CENTRE);//把水平对齐方式指定为居中
wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//把垂直对齐方式指定为居中
jxl.write.LabellabelC=newjxl.write.Label(0,1,"ThisisaLabelcell",wcfF);
sheet.addCell(labelC);
//3.添加带有formatting的Number对象
jxl.write.NumberFormatnf=newjxl.write.NumberFormat("#.##");
jxl.write.WritableCellFormatwcfN=newjxl.write.WritableCellFormat(nf);
jxl.write.NumberlabelNF=newjxl.write.Number(0,2,3.1415926,wcfN);
sheet.addCell(labelNF);
//4.添加Boolean对象
jxl.write.BooleanlabelB=newjxl.write.Boolean(0,3,false);
sheet.addCell(labelB);
//5.设置一个注解
WritableCellFeaturescellFeatures=newWritableCellFeatures();
cellFeatures.setComment("添加Boolean对象");
labelB.setCellFeatures(cellFeatures);
//6.单元格内换行
WritableCellFormatwrappedText=newWritableCellFormat
(WritableWorkbook.ARIAL_10_PT);
wrappedText.setWrap(true);//可换行的label样式
Labellabel=newLabel(4,0,"测试,\012测试。
。
。
",wrappedText);//"\012"强制换行
sheet.addCell(label);
//7.数字的公式计算
Numbern=newjxl.write.Number(0,9,4.5);//A10
sheet.addCell(n);
n=newNumber(1,9,8);//B10
sheet.addCell(n);
NumberFormatdp3=newNumberFormat("#.###");//设置单元格里面的数字格式
WritableCellFormatdp3cell=newWritableCellFormat(dp3);
dp3cell.setWrap(true);
Formulaf=newFormula(2,9,"(a10+b10)/2",dp3cell);//设置C10公式
sheet.addCell(f);
f=newFormula(3,9,"SUM(A10:
B10)",dp3cell);//设置D10公式
sheet.addCell(f);
//8.设置sheet的样式
sheet.getSettings().setProtected(true);//设置xls的保护,单元格为只读的
sheet.getSettings().setPassword("123");//设置xls的密码
sheet.getSettings().setDefaultColumnWidth(10);//设置列的默认宽度,2cm左右
sheet.setRowView(3,200);//设置第4行高度
sheet.setRowView(2,false);//这样可以自动把行高扩展
sheet.setColumnView(0,300);//设置第1列宽度,6cm左右
sheet.mergeCells(0,5,1,7);//合并单元格:
合并A6B8也就是1列6行与2列7行之间的矩形
//9.设置边框
drawRect(sheet,5,6,7,6,BorderLineStyle.THICK,Colour.BLAC