POI读写excel

来源：互联网发布：label mx生成软件编辑：程序博客网时间：2024/05/18 00:26

POI读写Excel文件

import java.io.FileInputStream;import java.io.FileOutputStream;import java.util.Date;import java.util.Iterator;import org.apache.poi.hssf.extractor.ExcelExtractor;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFHyperlink;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.util.CellRangeAddress;import org.junit.Test;public class ExcelTest {@Testpublic void testWrite() throws Exception{  // 创建Excel的工作书册 Workbook,对应到一个excel文档HSSFWorkbook excel =new HSSFWorkbook();  // 创建Excel的工作sheet,对应到一个excel文档的tabHSSFSheet sheet =excel.createSheet("第一个sheet");// 设置excel每列宽度sheet.setColumnWidth(0, 300);sheet.setColumnWidth(1, 500);  // 创建字体样式HSSFFont font =excel.createFont();font.setColor(IndexedColors.BLUE.getIndex());font.setFontName("华文宋体");font.setFontHeight((short) 300);font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 创建单元格样式HSSFCellStyle  style=excel.createCellStyle();style.setAlignment(CellStyle.ALIGN_CENTER);style.setWrapText(true);style.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);style.setFillPattern(CellStyle.SOLID_FOREGROUND);  // 设置边框   style.setBottomBorderColor(HSSFColor.RED.index);   style.setBorderBottom(CellStyle.BORDER_MEDIUM);   style.setBorderBottom(HSSFCellStyle.BORDER_THIN);   style.setBorderLeft(HSSFCellStyle.BORDER_THIN);   style.setBorderLeft(CellStyle.BORDER_DOTTED);   style.setBorderRight(HSSFCellStyle.BORDER_THIN);style.setRightBorderColor(IndexedColors.INDIGO.index);   style.setBorderTop(HSSFCellStyle.BORDER_THIN);      style.setFont(font);// 设置字体   // 创建Excel的sheet的一行   HSSFRow row =sheet.createRow(0);   row.setHeight((short) 500);// 设定行的高度      // 合并单元格CellRangeAddress(startRow，endRow，startColumn，endColumn)    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));       HSSFCell cell =row.createCell(0);   cell.setCellStyle(style);   cell.setCellValue("hello");      HSSFCellStyle style2 =excel.createCellStyle();   style2.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));   style2.setWrapText(true);// 自动换行      HSSFRow row2 =sheet.createRow(1);    HSSFCell cell2=row2.createCell(0);    cell2.setCellStyle(style2);   cell2.setCellValue(new Date());       // 创建超链接   HSSFHyperlink link =new HSSFHyperlink(HSSFHyperlink.LINK_URL);   link.setAddress("http://www.baidu.com");   HSSFCell cell3 =row2.createCell(1);   cell3.setCellValue("纵里寻她千百度");   cell3.setHyperlink(link);// 设定单元格的链接      FileOutputStream outputStream =new FileOutputStream("f:\\write.xls");   excel.write(outputStream);   outputStream.close();}@Testpublic void testRead() throws Exception{  //InputStream inputStream = EnrollAction.class.getResourceAsStream("enroll_template.xls");         //设置要读取的文件路径         POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("f:\\write.xls"));                  //HSSFWorkbook相当于一个excel文件，HSSFWorkbook是解析excel 2007之前的版本（xls）         //之后版本使用SXSSFWorkbook（xlsx）         HSSFWorkbook wb = new HSSFWorkbook(fs);         //获得sheet工作簿         HSSFSheet sheet = wb.getSheetAt(0);      //   wb.removeSheetAt(0);         //获得行         HSSFRow row = sheet.getRow(1);         //获得行中的列，即单元格         HSSFCell cell = row.getCell(0);                  //获得单元格中的值，若果该单元格的值为数字，所以使用getNumericCellValue，如为字符串则会报错         //如何取别的值，见print2方法         double msg = cell.getNumericCellValue();         System.out.println(msg);         print1(wb);         print2(wb);}private void print2(HSSFWorkbook wb) { HSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> iter = sheet.rowIterator();         //迭代行         while( iter.hasNext()) {             Row row = iter.next();             Iterator<Cell> cellIterator =  row.cellIterator();             //迭代列             while (cellIterator.hasNext()) {                 Cell cell = cellIterator.next();                 //用于测试的文件就2列，第一列为数字，第二列为字符串                 //对于数字cell.getCellType的值为HSSFCell.CELL_TYPE_NUMERIC，为0                 //对于字符串cell.getCellType的值为HSSFCell.CELL_TYPE_STRING，为1                 String content = cell.getCellType()== HSSFCell.CELL_TYPE_NUMERIC?cell.getNumericCellValue()+"":cell.getStringCellValue();                 System.out.println(content);             }         }}private void print1(HSSFWorkbook wb) {  //A text extractor for Excel files.        //Returns the textual content of the file, suitable for indexing by something like Lucene,        //but not really intended for display to the user. //用来获得整个excel文件的内容，表示为字符串        ExcelExtractor extractor = new ExcelExtractor(wb);        //字符串所包含的类型，详见api        extractor.setIncludeSheetNames(true);        extractor.setFormulasNotResults(false);        extractor.setIncludeCellComments(true);        //获得字符串形式        String text = extractor.getText();        System.out.println(text);}}

文件下载方法1：

先在服务器产生临时文件，再下载临时文件。

  关闭保存excel文件    FileOutputStream fOut = new FileOutputStream(xlsFile);        //创建xls文件，无内容 0字节    wb.write(fOut);                            //写内容，xls文件已经可以打开    fOut.flush();                            //刷新缓冲区    fOut.close();                            //关闭

文件下载方法2：

  //7.生成excel文件        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();            //生成流对象        wb.write(byteArrayOutputStream);                                //将excel写入流        //工具类，封装弹出下载框：                String outFile = "excelFile.xls";        DownloadBaseAction down = new DownloadBaseAction();        down.download(byteArrayOutputStream, response, outFile);

文件下载方法3：（适用于struts2）

   ServletActionContext.getResponse().setContentType("application/octet-stream");        String returnName = ServletActionContext.getResponse().encodeURL( new String("excelFile.xls".getBytes(), "ISO-8859-1"));        ServletActionContext.getResponse().addHeader("Content-Disposition", "attachment;filename=" + returnName);        wb.write(ServletActionContext.getResponse().getOutputStream());

文件下载方法4：

        //下载文件        response.setContentType("application/octet-stream");        String returnName = response.encodeURL( new String("生产厂家通讯录.xls".getBytes(), "ISO-8859-1"));        response.addHeader("Content-Disposition", "attachment;filename=" + returnName);        wb.write(response.getOutputStream());

0 0