2013-10-10 09:09:01
一.excel操作简介:java操作excel有POI和JXL两种方式
1.性能比较:POI的效率和稳定性比JXL高
2.功能比较:POI对excel中的公式支持比JXL好
3.环境支持:POI支持jdk1.5而jxl只支持jdk1.6或以上且不能在webShpere下运行
4.使用选择:POI
二.POI:
1.POI简介:POI可用两种模式(用户模式和事件用户模式)读取文件,使用POI时JVM最好设置为-Xms512m -Xmx1024m否则会很影响性能
(1).用户模式以DOM方式读取文件,其一次性将文件加载到内存所以内存占用大,不适合读取大数据量的文件,但读取操作方便
(2).事件用户模式以SAX方式读取文件,其每次只读取陪份数据所以内存占用少,适合用于读取大数据量的文件,但读操作不方便
2.创建简单的excel文件对象并输出
(1).创建一个无任何内容的excel工作表:HSSFWorkbook workBook = new HSSFWorkbook();
(2).在已创建的excel工作表对象中创建一个图表:HSSFSheet sheet = HSSFWorkbook对象.createSheet();
(3).在已创建的图表中创建行:HSSFRow row = HSSFSheet对象.createRow((short)从0开始的行号);
(4).在已创建的行中创建单元格:HSSFCell cell = HSSFRow对象.createCell((short)从0开始的列号);
(5).将创建和设置好的工作表输出到硬盘:HSSFWorkbook对象.write(new FileOutputStream("保存的路径+文件名"));
3.读取excel文件
(1).以文件输入流对象获取工作表对象:HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("文件路径"));
(2).用已获取的工作表对象获取指定的图表对象:HSSFSheet sheet = HSSFWorkbook对象.getSheetAt(int 指定图表的索引);
(3).用已获取的图表对象获取指定的行:HSSFRow row = HSSFSheet对象.getRow(int 指定行的索引);
(4).用已获取的行对象获取指定的单元格:HSSFCell cell = HSSFRow对象.getCell((short) 指定的单元格索引);
(5).获取单元格中的值:
①.先判断当前单元格的数据类型:HSSFCell对象.getCellType() == 正整数/HSSFCell.CELL_TYPE_DataType
②.根据单元格的数据类型获取相应的数据:
HSSFCell对象.getStringCellValue(); //获取String型单元格的数据
HSSFCell对象.getNumericCellValue(); //获取数值类型单元格的内容
如果当前单元格的数据类型是公式(HSSFCell.CELL_TYPE_FORMULA)则先将该单元格的数据类型设置为数值型(HSSFCell对象.setCellType(0/HSSFCell.CELL_TYPE_NUMERIC))再用获取数值型单元格的方式获取值
如果单元格的数据是超长的数值性,则按下面方式获取:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
double d = cell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
value = sf.format(date);
} else {
long cellData = (long) cell.getNumericCellValue();
if (cellData == cell.getNumericCellValue()) {
value = String.valueOf(cellData);
} else {
value = String.valueOf(cell.getNumericCellValue()).trim();
}
}
4.修改/设置execl
(1).基本设置
HSSFRow对象.setHeight((short)上百的行高值); //设置行高
HSSFRow对象.setRowHeight(2); //设置行高
HSSFSheet对象.setColumnWidth((short)列索引,(short)上百的列宽值); //设置列宽
HSSFCell对象.setCellType(HSSFCell.CELL_TYPE_DataType/正整数(0:int型,1:String型...)); //设置单元格的数据类型
Region region = new Region((short)开始行索引,(short)开始列索引,(short)结束行索引,(short)结束列索引); //创建一个从"开始行索引"和"开始列索引"到"结束行索引"和"结束列索引"的单元格合并对象
HSSFSheet对象.addMergedRegion(Region对象); //合并单元格
HSSFSheet对象.getNumMergedRegions(); //获得所有区域
HSSFWorkbook对象.setPrintArea(0, "$A$1:$C$2"); //设置打印区域
(2).单元格设置:
①.基本设置
HSSFCell对象.setCellValue("cellValue"); //给单元格设置内容
HSSFCell对象.setCellStyle(cellStyle对象); //给单元格设置样式
HSSFCell对象.setEncoding((short) 1); //给单元格设置编码(0:英文编码,1:中文编码)
②.字体设置:HSSFFont ft = HSSFWorkbook对象.createFont(); //创建字体对象,在将字体对象传入HSSFCellStyle对象.setFont()方法中
HSSFFont对象.setFontHeightInPoints((short) 字号数); //设置字号
HSSFFont对象.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗字体
HSSFFont对象.setColor(HSSFColor.RED.index); //设置字体颜色
③.样式设置:HSSFCellStyle style = HSSFWorkbook对象.createCellStyle()/HSSFCell对象.getCellStyle(); //创建/获得单元格样式
HSSFCellStyle对象.setFont(HSSFFont对象); //设置字体风格
HSSFCellStyle对象.setWrapText(true); //设置自动换行
HSSFCellStyle对象.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框
HSSFCellStyle对象.setBorderBottom(HSSFCellStyle.BORDER_DOTTED/HSSFCellStyle.BORDER_THIN); //下边框
HSSFCellStyle对象.setBorderLeft(HSSFCellStyle.BORDER_DOTTED); //左边框
HSSFCellStyle对象.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框
HSSFCellStyle对象.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置单元格内容左右居中
HSSFCellStyle对象.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置单元格内容上下居中
HSSFCellStyle对象.setRotation(short rotation); //设置单元格内容旋转rotation度
HSSFCellStyle对象.setFillForegroundColor(HSSFColor.LIME.index/HSSFColor.ORANGE.index); //设置单元格前景色(注:一定要指定填充方式),前景色并非字体颜色
HSSFCellStyle对象.setFillBackgroundColor(HSSFColor.AQUA.index); //设置单元格背景色,如果要同时设置前景色和背景色要先设置前景色,(注:一定要指定填充方式)
HSSFCellStyle对象.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND/HSSFCellStyle.BIG_SPOTS);
HSSFCell对象.setCellStyle(HSSFCellStyle);
(3).图表设置
HSSFSheet对象.createFreezePane(int 垂直线线标,int 水平线线标); //设置窗口冻结
HSSFSheet对象.setSelected(true); //设置默认选中哪个图表
HSSFSheet对象.setZoom(1,2); //50 percent magnification 设置图表的尺寸
HSSFSheet对象.setSheetName(图表索引,"名称",编码(HSSFCell.ENCODING_UTF_16)); //设置图表名
sheet.shiftRows(5, 10, -5); //清空行数据,调整行位置
(4).页眉页脚设置
HSSFHeader header = HSSFSheet对象.getHeader(); //得到页眉对象
HSSFHeader对象.setCenter("Center Header");
HSSFHeader对象.setLeft("Left Header");
HSSFHeader对象.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
HSSFFooter footer = HSSFSheet对象.getFooter(); //得到页脚对象
HSSFFooter对象.setCenter("Center Header");
HSSFFooter对象.setLeft("Left Header");
HSSFFooter对象.setRight("Page"+HSSFFooter.page()+"of"+HSSFFooter.numPages());
(5).调整工作表位置
HSSFPrintSetup ps = HSSFSheet对象.getPrintSetup();
HSSFSheet对象.setAutobreaks(true);
HSSFPrintSetup对象.setFitHeight((short)1);
HSSFPrintSetup对象.setFitWidth((short)1);
(6).插入图片
①.先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
ImageIO.write(bufferImg,"jpg",byteArrayOut);
②.将图片写入excel
HSSFPatriarch patriarch = HSSFSheet对象.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);
HSSFPatriarch对象.createPicture(HSSFClientAnchor对象,HSSFWorkbook对象.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
5.poi的常用方法
HSSFWorkbook对象.getNumberOfSheets(); //获取工作表中的图表数
int rowCount = HSSFSheet对象.getLastRowNum()/getPhysicalNumberOfRows(); //获取图表中的行数
int colCount = HSSFRow对象.getLastCellNum(); //获取当前行的列数
int firstRowNum = HSSFSheet对象.getFirstRowNum(); //获取第一行行号
POI中单元格的数据类型的枚举值:
HSSFCell.CELL_TYPE_NUMERIC //数值型
HSSFCell.CELL_TYPE_STRING //字符串型
HSSFCell.CELL_TYPE_FORMULA //公式型
HSSFCell.CELL_TYPE_BLANK //空值
HSSFCell.CELL_TYPE_ERROR //故障
三.JXL:
1.创建简单excel文件对象并输出
(1).以文件/输入流对象新建工作表对象:WritableWorkbook workbook = Workbook.createWorkbook(new File("输出路径+文件名.xls")/inputStream);
(2).用已新建的工作表对象创建图表对象:WritableSheet sheet = workbook.createSheet("sheetName", pageNumber); //用工作表对象新建指定名称和页数的图表对象
(3).实例化(新建)单元格对象:
模式1.实例化保存字符串的单元格对象:Label label = new Label(rowNumber,columnNumber,"content",[WritableCellFormat对象]); //以指定的单元格(前两个参数)、内容(第三个参数)及单元格格式化对象创建单元格对象
模式2.实例化保存数字的单元格对象:jxl.write.Number number = new jxl.write.Number(rowNumber,columnNumber,number,[WritableCellFormat对象]); //以指定单元格(前两个参数)、内容(第三个参数)及单元格格式化对象创建一个保存数字的单元格对象(必须使用Number的完整包路径,否则有语法歧义)
模式3.实例化保存日期的单元格对象:jxl.write.DateTime dt = new jxl.write.DateTime(rowNumber,columnNumber,dateObject,[WritableCellFormat对象]);
(4).将创建好的单元格添加到图表中:sheet.addCell(Label/Number对象); //将创建好的单元格添加到图表中
(5).将工作表输出:workbook.write(); //将工作表对象写到指定路径中
workbook.close(); //关闭workbook输出流,释放所占用的内存
2.读取excel文件
(1).以现有文件/输入流获取工作表对象:Workbook workbook = Workbook.getWorkbook(new File("文件路径")/inputStream); //以指定文件/输入流对象获取工作表对象
(2).用已获取的工作表对象获取图表对象:Sheet sheet = workbook.getSheet(0); //用已获取的工作表对象获取指定页数的图表对象
①.sheet.getRows(); //获取图表中的行数
②.sheet.getColumns(); //获取图表中的列数
sheet.getName(); //获取图表名
③.workbook.getNumberOfSheets(); //获取工作表中的图表个数
④.Sheet[] sheets = workbook.getSheets(); //获取工作表中的所有图表对象
Cell[] cells = sheet.getColumn(0); //获取指定列的所有单元格
Cell[] cells = sheet.getRow(0); //获取指定行的所有单元格
Cell cell = sheet.getCell(0,0); //获取指定单元格的对象引用(第一个参数是列数,第二个参数是行数)
(3).用已获取的图表对象获取单元格对象后再获取单元格中的数据:
方案1.获取总的单元格对象:Cell cell = sheet.getCell(0,0); //用已获取的图表对象获取指定行数和列数的单元格对象
String result = cell.getContents(); //获取单元格中的内容
方案2.获取字符串型单元格对象:LabelCell labelCell = (LabelCell)sheet.getCell(0,0);
String result = labelCell.getString(); //获取单元格中的内容
方案3.获取数字型单元格对象:NumberCell numberCell = (NumberCell)sheet.getCell(0,0);
String result = numberCell.getValue(); //获取单元格中的内容
方案4.获取日期型单元格对象:DateCell dateCell = (DateCell)sheet.getCell(0,0);
String result = dateCell.getDate(); //获取单元格中的内容
①.CellType.LABEL/NUMBER/DATE; //获取系统常量所属的数据类型
②.cell.getType(); //获取单元格中数据的类型,一般用于与CellType.LABEL/NUMBER/DATE比较
(5).关闭创建的workbook对象:workbook.close(); //关闭workbook对象,释放读取数据表的过程中所占用的内存
3.修改/设置excel
(1).单元格操作
<1>.设置单元格中内容的字体:
①.实例化字符串/数字/日期格式对象:
WritableFont wf = new WritableFont(WritableFont.TIMES/WritableFont.createFont("字体名"),16,WritableFont.BOLD); //以指定字体、字号、字体风格实例化字体对象
NumberFormat nf = new NumberFormat("#.##");
jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss");
②.实例化单元格格式化对象:WritableCellFormat wcf = new WritableCellFormat(字符串/数字/日期格式对象); //以指定字符串/数字/日期格式对象实例化单元格格式化对象
③.设置格式:wcf.setAlignment(jxl.format.Alignment.CENTRE); //设置水平对齐方式
wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置垂直对齐方式
wcf.setWrap(true); //设置自动换行
④.将单元格格式化对象添加的单元格对象中:Label label = new Label(0,0,"content",wcf);
<2>.合并单元格:WritableSheet对象.mergeCells(rowNumber,columnNumber,rowNumber,columnNumber); //合并指定单元格到指定单元格的所有单元格 注:合并后的单元格不能再次进行合并,否则触发异常
<3>.设置行高和列宽:WritableSheet对象.setRowView(rowNumber,rowHeight); //将指定行的高度设为指定高度
WritableSheet对象.setColumnView(columnNumber,columnWidth); //将指定列的宽度设为指定宽度