POI读写excel

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 {

	@Test
	public void testWrite() throws Exception{
		  // 创建Excel的工作书册 Workbook,对应到一个excel文档
		HSSFWorkbook excel =new HSSFWorkbook();
		  // 创建Excel的工作sheet,对应到一个excel文档的tab
		HSSFSheet 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();
	}
	
	
	@Test
	public 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());