二、单元格操作 Excel中很重要的一部分是对单元格的操作，比如行高、列宽、单元格合并等，所幸jExcelAPI提供了这些支持。这些操作相对比较简单，下面只介绍一下相关的API。 1、 合并单元格 WritableSheet.mergeCells(int m,int n,int p,int q); 作用是从(m,n)到(p,q)的单元格全部合并，比如： WritableSheet sheet=book.createSheet(“第一页”,0); //合并第一列第一行到第六列第一行的所有单元格 sheet.mergeCells(0,0,5,0); 合并既可以是横向的，也可以是纵向的。合并后的单元格不能再次进行合并，否则会触发异常。 2、 行高和列宽 WritableSheet.setRowView(int i,int height); 作用是指定第i+1行的高度，比如： //将第一行的高度设为200 sheet.setRowView(0,200); WritableSheet.setColumnView(int i,int width); 作用是指定第i+1列的宽度，比如： //将第一列的宽度设为30 sheet.setColumnView(0,30); 五、操作图片 public static void write()throws Exception{ WritableWorkbook wwb=Workbook.createWorkbook(new File("c:/1.xls")); WritableSheet ws=wwb.createSheet("Test Sheet 1",0); File file=new File("C:\\jbproject\\PVS\\WebRoot\\weekhit\\1109496996281.png"); WritableImage image=new WritableImage(1, 4, 6, 18,file); ws.addImage(image); wwb.write(); wwb.close(); } 很简单和插入单元格的方式一样，不过就是参数多了些，WritableImage这个类继承了Draw，上面只是他构造方法的一种，最后一个参数不用了说了，前面四个参数的类型都是double，依次是 x, y, width, height,注意，这里的宽和高可不是图片的宽和高，而是图片所要占的单位格的个数，因为继承的Draw所以他的类型必须是double，具体里面怎么实现的我还没细看：）因为着急赶活，先完成功能，其他的以后有时间慢慢研究。以后会继续写出在使用中的心得给大家。 读: 读的时候是这样的一个思路,先用一个输入流(InputStream)得到Excel文件,然后用jxl中的Workbook得到工作薄,用Sheet从工作薄中得到工作表,用Cell得到工作表中得某个单元格. InputStream->Workbook->Sheet->Cell,就得到了excel文件中的单元格 代码: String path="c:\\excel.xls";//Excel文件URL InputStream is = new FileInputStream(path);//写入到FileInputStream jxl.Workbook wb = Workbook.getWorkbook(is); //得到工作薄 jxl.Sheet st = wb.getSheet(0);//得到工作薄中的第一个工作表 Cell cell=st.getCell(0,0);//得到工作表的第一个单元格,即A1 String content=cell.getContents();//getContents()将Cell中的字符转为字符串 wb.close();//关闭工作薄 is.close();//关闭输入流 我们可以通过Sheet的getCell(x,y)方法得到任意一个单元格,x,y和excel中的坐标对应. 例如A1对应(0,0),A2对应(0,1),D3对应(3,2).Excel中坐标从A,1开始,jxl中全部是从0开始. 还可以通过Sheet的getRows(),getColumns()方法得到行数列数,并用于循环控制,输出一个sheet中的所有内容. 写: 往Excel中写入内容主要是用jxl.write包中的类. 思路是这样的: OutputStream<-WritableWorkbook<-WritableSheet<-Label 这里面Label代表的是写入Sheet的Cell位置及内容. 代码: OutputStream os=new FileOutputStream("c:\\test.xls");//输出的Excel文件URL WritableWorkbook wwb = Workbook.createWorkbook(os);//创建可写工作薄 WritableSheet ws = wwb.createSheet("sheet1", 0);//创建可写工作表 Label labelCF=new Label(0, 0, "hello");//创建写入位置和内容 ws.addCell(labelCF);//将Label写入sheet中 Label的构造函数Label(int x, int y,String aString)xy意同读的时候的xy,aString是写入的内容. WritableFont wf = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false);//设置写入字体 WritableCellFormat wcfF = new WritableCellFormat(wf);//设置CellFormat Label labelCF=new Label(0, 0, "hello");//创建写入位置,内容和格式 Label的另一构造函数Label(int c, int r, String cont, CellFormat st)可以对写入内容进行格式化,设置字体及其它的属性. 现在可以写了 wwb.write(); 写完后关闭 wwb.close(); 输出流也关闭吧 os.close; OK,只要把读和写结合起来,就可以在N个Excel中读取数据写入你希望的Excel新表中,还是比较方便的. 下面是程序一例: 程序代码:sql = "select * from tablename"; rs = stmt.executeQuery(sql); //新建Excel文件 String filePath=request.getRealPath("aaa.xls"); File myFilePath=new File(filePath); if(!myFilePath.exists()) myFilePath.createNewFile(); FileWriter resultFile=new FileWriter(myFilePath); PrintWriter myFile=new PrintWriter(resultFile); resultFile.close(); //用JXL向新建的文件中添加内容 OutputStream outf = new FileOutputStream(filePath); jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(outf); jxl.write.WritableSheet ws = wwb.createSheet("sheettest", 0); int i=0; int j=0; for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) { ws.addCell(new Label(k,0,rs.getMetaData().getColumnName(k+1))); } while(rs.next()){ out.println(rs.getMetaData().getColumnCount()); for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) { ws.addCell(new Label(k,j+i+1,rs.getString(k+1))); } i++; } wwb.write(); wwb.close(); }catch(Exception e){e.printStackTrace();} finally{ rs.close(); conn.close(); } response.sendRedirect("aaa.xls"); ============================================== 1 /** 2 * QuickExcel.java 3 * 作者：杨庆成 4 * Created on 2004年11月22日, 下午4:05 5 * 在实际应用中经常要将数据库中的表导入Excel 6 * 本人在Apache的POI基础上写了一个简单的类 7 * 有不当指出请指正，谢谢！ 8 * 9 */ 10 11package yqc.poi; 12 13import java.sql.*; 14import java.util.*; 15import java.io.*; 16import java.io.ByteArrayInputStream; 17import java.io.FileInputStream; 18import java.io.FileOutputStream; 19import java.io.IOException; 20 21import org.apache.poi.hssf.usermodel.*; 22import org.apache.poi.poifs.filesystem.POIFSFileSystem; 23import org.apache.poi.hssf.record.*; 24import org.apache.poi.hssf.model.*; 25import org.apache.poi.hssf.usermodel.*; 26import org.apache.poi.hssf.util.*;import yqc.sql.*; 27 28/**//** 29 * 30 * @author Administrator 31 */ 32public class QuickExcel { 33 34 /**//** Creates a new instance of QuickExcel */ 35 private QuickExcel(String file){ 36 _file=file; 37 } 38 39 private void open()throws IOException{ 40 InputStream stream = null; 41 Record[] records = null; 42 POIFSFileSystem fs = 43 new POIFSFileSystem(new FileInputStream(_file)); 44 _wb = new HSSFWorkbook(fs); 45 } 46 47 private void create(){ 48 _wb=new HSSFWorkbook(); 49 } 50 51 public static QuickExcel newInstance (String file){ 52 QuickExcel qe=new QuickExcel(file); 53 qe.create(); 54 return qe; 55 } 56 57 public static QuickExcel openInstance(String file) throws IOException { 58 QuickExcel qe=new QuickExcel(file); 59 qe.open(); 60 return qe; 61 } 62 63 public void close(){ 64 try{ 65 FileOutputStream fileOut = new FileOutputStream(_file); 66 _wb.write(fileOut);//把Workbook对象输出到文件workbook.xls中 67 fileOut.close(); 68 } 69 catch (Exception ex){ 70 System.out.println(ex.getMessage()); 71 } 72 } 73 74 private void removeSheet(String sheetName){ 75 int i=_wb.getSheetIndex("sheetName"); 76 if (i>=0) _wb.removeSheetAt(i); 77 } 78 79 public int fillSheet (ResultSet rs,String sheetName)throws SQLException { 80 HSSFSheet st= _wb.createSheet(sheetName); 81 ResultSetMetaData rsmd= rs.getMetaData(); 82 int index=0; 83 int result=0; 84 HSSFRow row=st.createRow(index++); 85 for(int i=1;i<=rsmd.getColumnCount();++i){ 86 HSSFCell cell=row.createCell((short)(i-1)); 87 cell.setCellValue(rsmd.getColumnName(i)); 88 } 89 while(rs.next()) { 90 result++; 91 row=st.createRow(index++); 92 for(int i=1;i<=rsmd.getColumnCount();++i){ 93 HSSFCell cell=row.createCell((short)(i-1)); 94 cell.setEncoding(cell.ENCODING_UTF_16); 95 cell.setCellValue(rs.getString(i)); 96 } 97 } 98 return result; 99} 100 101 public static void main(String[] args){ 102 try{ 103 QuickConnection qc=new MssqlConnection("jdbc:microsoft:sqlserver://192.168.0.100:1433;DatabaseName=ls"); 104 QuickExcel qe=QuickExcel.newInstance("a.xls"); 105 qc.connect(); 106 String sql="select * from ls.dbo.radio1_emcee"; 107 ResultSet rs=qc.getStatement().executeQuery(sql); 108 qe.fillSheet(rs,"MT"); 109 qe.close(); 110 qe=QuickExcel.openInstance("a.xls"); 111 qe.fillSheet(rs,"MO"); 112 qe.close(); 113 qc.close(); 114 } 115 catch (SQLException ex){ 116 System.out.println(ex.getMessage()); 117 } 118 catch (IOException ex){ 119 System.out.println(ex.getMessage()); 120 } 121 } 122 123 HSSFWorkbook _wb; 124 String _file="new.xls"; 125} =================================== /**导出数据为XLS格式 * @param fos 生成Excel文件Path * @param bo 要导入的数据 */ public void writeExcelBo(FileOutputStream fos, java.util.Vector ve) { jxl.write.WritableWorkbook wwb; try { wwb= Workbook.createWorkbook(fos); jxl.write.WritableSheet ws= wwb.createSheet("booksheet", 10); ws.addCell(new jxl.write.Label(0, 1, "书目ID")); ws.addCell(new jxl.write.Label(1, 1, "ISBN")); ws.addCell(new jxl.write.Label(2, 1, "定价")); ws.addCell(new jxl.write.Label(3, 1, "书名")); ws.addCell(new jxl.write.Label(4, 1, "原书名")); ws.addCell(new jxl.write.Label(5, 1, "副题名")); ws.addCell(new jxl.write.Label(6, 1, "著者")); ws.addCell(new jxl.write.Label(7, 1, "译者")); ws.addCell(new jxl.write.Label(8, 1, "版次")); ws.addCell(new jxl.write.Label(9, 1, "出版地")); ws.addCell(new jxl.write.Label(10, 1, "出版社")); ws.addCell(new jxl.write.Label(11, 1, "出版日期")); ws.addCell(new jxl.write.Label(12, 1, "页数")); ws.addCell(new jxl.write.Label(13, 1, "书高")); ws.addCell(new jxl.write.Label(14, 1, "装帧")); ws.addCell(new jxl.write.Label(15, 1, "丛书名")); ws.addCell(new jxl.write.Label(16, 1, "一般性附注项")); ws.addCell(new jxl.write.Label(17, 1, "简介")); ws.addCell(new jxl.write.Label(18, 1, "主题词")); ws.addCell(new jxl.write.Label(19, 1, "中图法分类")); ws.addCell(new jxl.write.Label(20, 1, "更新日期")); ws.addCell(new jxl.write.Label(21, 1, "本数")); book=new Book[ve.size()]; for (int i= 0; i < ve.size(); i++) { book[i]= (Book)ve.get(i); ws.addCell(new jxl.write.Label(0, i + 2, "" + book[i].getBookId())); ws.addCell(new jxl.write.Label(1, i + 2, book[i].getIsbn())); ws.addCell(new jxl.write.Label(2, i + 2, "" + book[i].getPrice())); ws.addCell(new jxl.write.Label(3, i + 2, book[i].getBookTitle())); ws.addCell(new jxl.write.Label(4, i + 2, book[i].getOldFilename())); ws.addCell(new jxl.write.Label(5, i + 2, book[i].getSubTitle())); ws.addCell(new jxl.write.Label(6, i + 2, book[i].getWriter())); ws.addCell(new jxl.write.Label(7, i + 2, book[i].getTranscribe())); ws.addCell(new jxl.write.Label(8, i + 2, "" + book[i].getVersion())); ws.addCell(new jxl.write.Label(9, i + 2, book[i].getPublishCity())); ws.addCell(new jxl.write.Label(10, i + 2, book[i].getPublisher())); ws.addCell(new jxl.write.Label(11, i + 2, book[i].getPublishDate().toString())); ws.addCell(new jxl.write.Label(12, i + 2, "" + book[i].getPage())); ws.addCell(new jxl.write.Label(13, i + 2, "" + book[i].getHight())); ws.addCell(new jxl.write.Label(14, i + 2, book[i].getInstall())); ws.addCell(new jxl.write.Label(15, i + 2, book[i].getSeries())); ws.addCell(new jxl.write.Label(16, i + 2, book[i].getNotes())); ws.addCell(new jxl.write.Label(17, i + 2, book[i].getPrecisnotes())); ws.addCell(new jxl.write.Label(18, i + 2, book[i].getSubject())); ws.addCell(new jxl.write.Label(19, i + 2, book[i].getCls().replaceAll("_", ""))); ws.addCell(new jxl.write.Label(20, i + 2, book[i].getUpdatedate().toString())); ws.addCell(new jxl.write.Label(21, i + 2, "0")); } jxl.write.WritableFont wfc= new jxl.write.WritableFont( WritableFont.ARIAL, 255, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); jxl.write.WritableCellFormat wcfFC= new jxl.write.WritableCellFormat(wfc); ws.addCell(new jxl.write.Label(0, 0, "为保证您提交定单的稳定和正确，导入定单时候请勿更改此表格式（请勿更改书目ID,订购本数自行添加！）")); wwb.write(); //关闭Excel工作薄对象 wwb.close(); } catch (IOException e) {} catch (RowsExceededException e) {} catch (WriteException e) {} } //导入EXCEL if (f.getName().indexOf(".xls") > 0) { try { fis= new FileInputStream(f); BookBean bob= new BookBean(); UserBean usb= new UserBean(); jxl.Workbook rwb= Workbook.getWorkbook(fis); jxl.Sheet sh= rwb.getSheet(0); int rowCount= sh.getRows(); SimpleDateFormat sdf= new SimpleDateFormat("dd/MM/yyyy"); book= new Book[rowCount - 1]; for (int i= 1; i < rowCount; i++) { book[i - 1]= new Book(); jxl.Cell[] ce= sh.getRow(i); book[i - 1].setIsbn(ce[0].getContents().toString()); book[i - 1].setSeries(ce[1].getContents().toString()); book[i - 1].setBookTitle(ce[2].getContents().toString()); book[i - 1].setWriter(ce[3].getContents().toString()); book[i - 1].setTranscribe(ce[4].getContents().toString()); book[i - 1].setPublisher(ce[5].getContents().toString()); book[i - 1].setPublishDate(sdf.parse(ce[6].getContents().toString(), new ParsePosition(0))); book[i-1].setVersion(Integer.parseInt(ce[7].getContents().toString())); book[i-1].setPage(Integer.parseInt(ce[8].getContents().toString())); book[i-1].setCls(ce[9].getContents().toString()); book[i-1].setPrecisnotes(ce[10].getContents().toString()); book[i-1].setInstall(ce[11].getContents().toString()); book[i-1].setPrice(Float.parseFloat(ce[12].getContents().toString())); book[i-1].setUserid(usb.getUser().getUserid()); getVector().addElement(book[i - 1]); } rwb.close(); fis.close(); } catch (FileNotFoundException e) {} catch (BiffException e) {} catch (IOException e) {} catch (NumberFormatException e) { ShowMessage("数据导入失败，请按照本软件要求的EXCEL格式导入定单"); } } ================================================= |
联系客服
