POIExcelServlet 处理Excel文件
java 代码
- package it;
- import java.io.*;
- import java.text.DecimalFormat;
- import javax.servlet.*;
- import javax.servlet.http.*;
- import org.apache.poi.hssf.usermodel.*;
- import java.util.Enumeration;
- public class POIExcelServlet extends HttpServlet {
- private String poiexcelPath = "";
- public void destroy() {
- super.destroy(); // Just puts "destroy" string in log
- // Put your code here
- }
- public void doGet(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- createExcel(this.poiexcelPath,request);
- readExcel(this.poiexcelPath,response);
- }
- public void doPost(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- doGet(request,response);
- }
- public void init(ServletConfig config) throws ServletException {
- super.init(config);
- poiexcelPath = "e:\\jsp\\telx.xls"; //给定保存路径
- }
- public void createExcel(String filename,HttpServletRequest request){
- String outputFile = filename;
- int i = 1;
- String name;
- String value;
- try{
- HSSFWorkbook workbook = new HSSFWorkbook(); //建立EXCEL关联文件
- HSSFSheet sheet = workbook.createSheet("New Sheet"); //新建SHEET对象
- HSSFRow row = sheet.createRow((short)0); //新建ROW对象
- HSSFCell cell = row.createCell((short)0); //新建CELL对象
- cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置CELL属性(字符)
- cell.setCellValue("Parameter Name"); //给定值
- cell = row.createCell((short)1);
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setCellValue("Parameter Value");
- Enumeration enum1 = request.getParameterNames();
- System.out.println(enum1.toString());
- while(enum1.hasMoreElements()){
- row = sheet.createRow((short)i);
- name = (String)enum1.nextElement();
- value = request.getParameter(name);
- cell = row.createCell((short)0);
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setCellValue(name);
- cell= row.createCell((short)1);
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setCellValue(value);
- i++;
- }
- FileOutputStream fos = new FileOutputStream(outputFile);
- workbook.write(fos); //通过流生成EXCEL文件
- fos.flush();
- fos.close();
- System.out.println("文件生成…");
- }catch(Exception e){
- System.out.println("error at createExcel"+e);
- }
- }
- public void readExcel(String filename,HttpServletResponse response) throws IOException{
- DecimalFormat df = new DecimalFormat("#"); //建立格式化数字对象(#)单个任何数字
- String fileToBeRead = filename;
- PrintWriter out = response.getWriter();
- try{
- String strCell = null;
- HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
- //给定构造函数为读入
- for(int numSheets = 0;numSheets
- if(null!=workbook.getSheetAt(numSheets)){
- HSSFSheet aSheet = workbook.getSheetAt(numSheets);
- for(int rowNumOfSheet = 0;rowNumOfSheet<=aSheet.getLastRowNum();rowNumOfSheet++){
- if(null!=aSheet.getRow(rowNumOfSheet)){
- HSSFRow aRow =aSheet.getRow(rowNumOfSheet);
- for(short cellNumOfRow = 0;cellNumOfRow<=aRow.getLastCellNum();cellNumOfRow++){
- if(null!=aRow.getCell(cellNumOfRow)){
- HSSFCell aCell = aRow.getCell(cellNumOfRow);
- int cellType = aCell.getCellType();
- switch(cellType){
- case 0:
- strCell = df.format(aCell.getNumericCellValue());
- out.println(strCell+" ");
- break;
- case 1:
- strCell = aCell.getStringCellValue();
- out.println(strCell+" ");
- break;
- default:
- out.println("格式错误");
- }
- }
- }
- out.print("
"); - }
- }
- }
- }
- }catch(Exception e){
- System.out.println("error at readExcel"+e);
- }
- }
- }