jxl导出Excel

jopen 6年前

首先先在自己工程中导入jxl的jar包;

import java.io.File;  import java.io.FileOutputStream;  import java.io.IOException;  import java.io.OutputStream;  import java.util.Vector;  import jxl.Cell;  import jxl.Sheet;  import jxl.Workbook;  import jxl.format.Alignment;  import jxl.format.Colour;  import jxl.format.UnderlineStyle;  import jxl.format.VerticalAlignment;  import jxl.write.Label;  import jxl.write.WritableCellFormat;  import jxl.write.WritableFont;  import jxl.write.WritableSheet;  import jxl.write.WritableWorkbook;  import jxl.write.WriteException;  import jxl.write.biff.RowsExceededException;  public class ExportData {      /** *//**       * 导出数据为XLS格式       * @param fileName        文件的名称,可以设为绝对路径,也可以设为相对路径       * @param content        数据的内容       */      public static void exportExcel(String fileName, Vector<Person> content) {          WritableWorkbook wwb;          FileOutputStream fos;          try {                  fos = new FileOutputStream(fileName);              wwb = Workbook.createWorkbook(fos);              WritableSheet ws = wwb.createSheet("三国志武将列表", 10);        // 创建一个工作表              //    设置单元格的文字格式              WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,                      UnderlineStyle.NO_UNDERLINE,Colour.BLUE);              WritableCellFormat wcf = new WritableCellFormat(wf);              wcf.setVerticalAlignment(VerticalAlignment.CENTRE);               wcf.setAlignment(Alignment.CENTRE);               ws.setRowView(1, 500);              //    填充数据的内容              Person[] p = new Person[content.size()];              for (int i = 0; i < content.size(); i++){                  p[i] = (Person)content.get(i);                  ws.addCell(new Label(1, i + 1, p[i].getName(), wcf));                  ws.addCell(new Label(2, i + 1, p[i].getNickname(), wcf));                  ws.addCell(new Label(3, i + 1, p[i].getPower(), wcf));                  ws.addCell(new Label(4, i + 1, p[i].getWit(), wcf));                  ws.addCell(new Label(5, i + 1, p[i].getPolity(), wcf));                  ws.addCell(new Label(6, i + 1, p[i].getCharm(), wcf));                  ws.addCell(new Label(7, i + 1, p[i].getStory(), wcf));                  if(i == 0)                      wcf = new WritableCellFormat();              }              wwb.write();              wwb.close();          } catch (IOException e){          } catch (RowsExceededException e){          } catch (WriteException e){}      }      /** *//**       * 从Excel文件里读取数据保存到Vector里       * @param fileName        Excel文件的名称       * @return                Vector对象,里面包含从Excel文件里获取到的数据       */      public static Vector<Person> importExcel(String fileName){          Vector<Person> v = new Vector<Person>();          try {              Workbook book = Workbook.getWorkbook(new File(fileName));              Sheet sheet = book.getSheet(0);        // 获得第一个工作表对象               int rows = sheet.getRows();                            for(int i = 0; i < rows; i++) {                  Cell [] cell = sheet.getRow(i);                  if(cell.length == 0)                      continue;                                    Person p = new Person();                  p.setName(sheet.getCell(1, i).getContents());                  p.setNickname(sheet.getCell(2, i).getContents());                  p.setPower(sheet.getCell(3, i).getContents());                  p.setWit(sheet.getCell(4, i).getContents());                  p.setPolity(sheet.getCell(5, i).getContents());                  p.setCharm(sheet.getCell(6, i).getContents());                  p.setStory(sheet.getCell(7, i).getContents());                  v.add(p);              }              book.close();          }catch(Exception e) {}           return v;      }      public static void main(String [] args){          String fileName = "C:\\test.xls";          String fileNameNew = "C:\\testNew.xls";                    Person p0 = new Person("姓名","字","武力","智力","政治","魅力","英雄事迹");          Person p1 = new Person("赵云","子龙","98","84","83","87","单骑救主!!!");          Person p2 = new Person("马超","孟起","98","62","40","88","杀得曹操割须弃袍!!!");          Person p3 = new Person("诸葛亮","孔明","55","100","92","93","死后木偶退兵,锦囊杀魏延!!!");          Vector<Person> v = new Vector<Person>();          v.add(p0);          v.add(p1);          v.add(p2);          v.add(p3);                    exportExcel(fileName, v);          System.out.println("成功导出数据到Excel文件(" + fileName + ")了!!!");            //        Vector<Person> vector = importExcel(fileName);  //        System.out.println("成功从Excel文件(" + fileName + ")导入数据!!!");  //          //        exportExcel(fileNameNew, vector);  //        System.out.println("成功将" + fileName + "里的数据手复制到" + fileNameNew + "中!!!");      }  }

上面的方法很好,很好用,但它只实现了自动生成了excel文件;但是我们项目要求的是:弹出保存/打开excel的对话框,然后直接打开或输入路径再保存;
import java.io.IOException;  import java.io.OutputStream;  import java.util.HashMap;  import java.util.Vector;  import javax.servlet.http.HttpServletResponse;  import jxl.Workbook;  import jxl.format.Alignment;  import jxl.format.Colour;  import jxl.format.UnderlineStyle;  import jxl.format.VerticalAlignment;  import jxl.write.Label;  import jxl.write.WritableCellFormat;  import jxl.write.WritableFont;  import jxl.write.WritableSheet;  import jxl.write.WritableWorkbook;  import jxl.write.WriteException;  import jxl.write.biff.RowsExceededException;  public class ExportExcel {  public static void exportExcel( Object objIn,Object objOut) {           try {           //取得response HttpServletResponse           HashMap hmOut = (HashMap)objOut;           HashMap hmIn = (HashMap)objIn;           HttpServletResponse response=(HttpServletResponse)hmIn.get("response");           //设置table列名           String excelName =(String)hmIn.get("excelName");           String[] excelNameArray = excelName.split(",");                                 //取得key           String[] excelKeyArray = (String[])hmOut.get("excelKey");                      OutputStream os = response.getOutputStream();// 取得输出流                  response.reset();// 清空输出流                 response.setHeader("Content-disposition", "attachment; filename="+new String("Book1".getBytes("GB2312"),"8859_1")+".xls");// 设定输出文件头                  response.setContentType("application/msexcel");// 定义输出类型                               WritableWorkbook wwb = Workbook.createWorkbook(os); // 建立excel文件                             WritableSheet ws = wwb.createSheet("Sheet1", 10);        // 创建一个工作表               //    设置单元格的文字格式               WritableFont wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,                       UnderlineStyle.NO_UNDERLINE,Colour.BLUE);               WritableCellFormat wcf = new WritableCellFormat(wf);               wcf.setVerticalAlignment(VerticalAlignment.CENTRE);                wcf.setAlignment(Alignment.CENTRE);                ws.setRowView(0, 500);               //    填充数据的内容               int len=((String[])hmOut.get(excelKeyArray[0])).length;               //设置列头名           for (int j=0;j<excelKeyArray.length;j++){              ws.addCell(new Label(j, 0, excelNameArray[j], wcf));           }           //设置内容           wcf = new WritableCellFormat();               for (int i = 0; i <len; i++){               for (int j=0;j<excelKeyArray.length;j++){                  ws.addCell(new Label(j, i+1, ((String[])hmOut.get(excelKeyArray[j]))[i], wcf));               }               }               wwb.write();               wwb.close();           } catch (IOException e){           } catch (RowsExceededException e){           } catch (WriteException e){}       }  }

注:现在弹出对话框,就是说要用io流的方式,io流就要从response中取;