使用Struts1从数据库下载，打印exsl表格

1、建立实体类

package Exsl;

import java.util.Date;

import org.apache.struts.action.ActionForm;

public class ARCHIVE extends ActionForm{

int ARCHIVE_ID;

int ARCHIVE_YEAR;

String ARCHIVE_TITLE;

String ARCHIVE_MARK;

Date START_DATE;

public int getARCHIVE_ID() {

return ARCHIVE_ID;

}

public void setARCHIVE_ID(int aRCHIVE_ID) {

ARCHIVE_ID = aRCHIVE_ID;

}

public int getARCHIVE_YEAR() {

return ARCHIVE_YEAR;

}

public void setARCHIVE_YEAR(int aRCHIVE_YEAR) {

ARCHIVE_YEAR = aRCHIVE_YEAR;

}

public String getARCHIVE_TITLE() {

return ARCHIVE_TITLE;

}

public void setARCHIVE_TITLE(String aRCHIVE_TITLE) {

ARCHIVE_TITLE = aRCHIVE_TITLE;

}

public String getARCHIVE_MARK() {

return ARCHIVE_MARK;

}

public void setARCHIVE_MARK(String aRCHIVE_MARK) {

ARCHIVE_MARK = aRCHIVE_MARK;

}

public Date getSTART_DATE() {

return START_DATE;

}

public void setSTART_DATE(Date sTART_DATE) {

START_DATE = sTART_DATE;

}

}

2、写入action  ---ExslAtion 

package Exsl;

import java.io.IOException;

import java.io.PrintWriter;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.ActionMapping;

import org.apache.struts.actions.DispatchAction;

public class ExslAtion extends DispatchAction {

public void getexsl(ActionMapping mapping, ActionForm form,

HttpServletRequest request, HttpServletResponse response) throws Exception{

ExslServlet exsl = new ExslServlet();

exsl.getarchive(response);

}

}

3、从数据库查询数据语句  ExslServlet

package Exsl;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.jsp.jstl.sql.Result;

import com.cn.servlet.BaseDAO;

public class ExslServlet {

public boolean getarchive(HttpServletResponse response) throws Exception{

String sql = "select * from ARCHIVE";

Result result =  BaseDAO.runSelectSql(sql);

List lists = new ArrayList();

Map[] rows = result.getRows(); 

String[] title={"序号","备注","页数","备注","日期"};

for(Map row:rows){

List list=new ArrayList();

list.add(row.get("ARCHIVE_ID"));

list.add(row.get("ARCHIVE_YEAR"));

list.add(row.get("ARCHIVE_MARK"));

list.add(row.get("ARCHIVE_TITLE"));

list.add(row.get("START_DATE"));

lists.add(list);

}

new OutExcel().outExcel(response,title,lists);

return false;

}

}

4、至关重要的一点就是写入下载exsl表格的封印方法

package Exsl;

import java.io.File;

import java.io.FileOutputStream;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.List;

import javax.servlet.http.HttpServletResponse;

//打出excel表格OutExcel 

public class OutExcel {

public boolean outExcel(HttpServletResponse response,String[] title,List lists){

Date date=new Date();

SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");

String fileName=sdf.format(date);

File f=new File("D://"+fileName+".xls");

try{

f.createNewFile();

Excel.writeExcel(new FileOutputStream(f),title,lists);

Files.exportFile(response, f, true);

return true;

}catch(Exception e){

e.printStackTrace();

return false;

}

}

}

5、封印打印exsl的方法里调用了两个方法  Excel和exportFile

package Exsl;

import java.io.IOException;

import java.io.OutputStream;

import java.util.List;

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

public class Excel {

public static void writeExcel(OutputStream os, String[] title, List lists) throws IOException, RowsExceededException, WriteException  {

 // 创建可以写入的Excel工作�?默认运行生成的文件在tomcat/bin�?)

 WritableWorkbook wwb = Workbook.createWorkbook(os);

 // 生成工作�?(name:First Sheet,参数0表示这是第一�?

 WritableSheet sheet = wwb.createSheet("First Sheet", 0);

 

       // �?��写入第一�?即标题栏)

       for (int i=0; i<title.length; i++) {

           // 用于写入文本内容到工作表中去

           Label label = null;

           // 在Label对象的构造中指明单元格位�?参数依次代表列数、行数�?内容 )      

           label = new Label(i, 0, title[i]);

           // 将定义好的单元格添加到工作表�?

           sheet.addCell(label);

       }

       // �?��写入内容

       for (int row=0; row<lists.size(); row++) {        

            // 获取�?��(�?��)记录

            List list = (List) lists.get(row);

            // 数据是文本时(用label写入到工作表�?

            for (int col=0; col<list.size(); col++) {                

              String listvalue = (String) list.get(col).toString(); 

              Label label = null;

              label = new Label(col, row+1, listvalue);

                    sheet.addCell(label);            

            }   

     } 

   

    /*

       生成�?��保存数字的单元格,必须使用Number的完整包路径,否则有语法歧�?值为789.123

   jxl.write.Number number = new jxl.write.Number(col, row, 555.12541);

   sheet.addCell(number);

 */

       /*

                          生成�?��保存日期的单元格,必须使用DateTime的完整包路径,否则有语法歧�?值为new Date()

         jxl.write.DateTime date = new jxl.write.DateTime(col, row, new java.util.Date());

         sheet.addCell(date);

        */

       // 写入数据

 wwb.write();

 // 关闭文件

 wwb.close();

 // 关闭输出�?

 os.close();

}

}

package Exsl;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.net.URLEncoder;

import javax.servlet.http.HttpServletResponse;

public class Files {

public static void exportFile(HttpServletResponse response, File file, boolean isDel) throws IOException {

 OutputStream out = null;

 InputStream in = null;

 

 // 获得文件�?

 String filename = URLEncoder.encode(file.getName(), "UTF-8"); 

 // 定义输出类型(下载)

    response.setContentType("application/force-download"); 

    response.setHeader("Location", filename);

    // 定义输出文件�?

 response.setHeader("Content-Disposition", "attachment;filename=" + filename); 

 out = response.getOutputStream();

 in = new FileInputStream(file.getPath()); 

 byte[] buffer = new byte[1024];

 int i = -1;

 while ((i = in.read(buffer)) != -1) {

   out.write(buffer, 0, i);

 }

 

 in.close();

 out.close();  

 

 if (isDel) {

  //删除文件,删除前关闭所有的Stream.

  file.delete();

 }

 

}

}

6、配置Struts的文件

7、写JSP

 <a href="exsl.do?method=getexsl">打印表格</a>

8、即可从数据库里打印出exsl表格，如图

转载于:https://my.oschina.net/u/2341995/blog/396886