当前页面: 开发资料首页 → JSP 专题 → .jsp数据导出到EXCEL中 老板催得紧 有谁做过给个成型代码谢谢了

.jsp数据导出到EXCEL中 老板催得紧 有谁做过给个成型代码谢谢了

摘要: .jsp数据导出到EXCEL中 老板催得紧 有谁做过给个成型代码谢谢了

从数据库中提取数据，在页面有个提交按钮，当提交时数据显示到EXCEL中

---

用POI
www.apache.org

---

如何通过点击网页上的一个按钮把从数据库中查询出来的数据以EXCEL的方式显示给用户,其中的数据该如何组织,希望各位高手讲解一下!


首先在标签库中加一个excel的包
在jsp中写下面的链接：

<input name="Submit" type="button" class="queryBox" value=" 打印报表 " onclick="javasript:window.open('printerservlet','report','toolbar=no,status=no,scollbars,resizable,top=1,left=1,width=800,height=600');">

在写一个'printerservlet'的servlet就ok了，如下：

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.util.Date;
import java.lang.Integer;
import java.io.File;
import jxl.Workbook;
import jxl.SheetSettings;
import jxl.WorkbookSettings;
import jxl.Range;
import jxl.write.WritableWorkbook;
import jxl.write.WritableSheet;
import jxl.write.WritableFont;
import jxl.write.WritableCellFormat;
import jxl.write.NumberFormats;
import jxl.write.DateFormats;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.DateTime;
import jxl.write.NumberFormat;
import jxl.write.DateFormat;
import jxl.write.WriteException;
import jxl.write.WritableHyperlink;
import jxl.write.Boolean;
import jxl.write.Formula;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.ScriptStyle;
import jxl.format.Orientation;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.jsp.*;



public class PrinterServlet extends HttpServlet {
private static final String CONTENT_TYPE = "text/html; charset=GBK";
//Initialize global variables
public void init() throws ServletException {
}
//Process the HTTP Get request
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType(CONTENT_TYPE);
PrintWriter out = response.getWriter();

HttpSession session=request.getSession(true);
int USERID=Integer.parseInt(session.getAttribute("USERID").toString());
booleanbExportSucc = false;
String sErrorStr="";

String sHead="./files/";
String sFileName=USERID+"Test.xls";
String sCurrPath=request.getRealPath("./files/");
File filepath = new File(sCurrPath);
if (filepath.exists()){
out.println("路径已经存在！
");
}else {
filepath.mkdir();
}
sCurrPath=sCurrPath+"/"+sFileName;
//String myfilepath = request.getRealPath(sCurrPath);
File file = new File(sCurrPath);
file.createNewFile();
//获取文件的URL地址
String sUrlPath=sHead+sFileName;

//以下开始输出到EXCEL
try {
/************创建工作簿*************/
WritableWorkbook workbook = Workbook.createWorkbook(new File(sCurrPath));


/************创建工作表*************/

WritableSheet sheet = workbook.createSheet("报表系统", 0);
//sheet.setPageSetup(5,true);


/************设置纵横打印（默认为纵打）、打印纸******************/
//sheet.setPageSetup(PageOrientation.LANDSCAPE.LANDSCAPE,0,0);
//sheet.setPageSetup(PageOrientation.LANDSCAPE.LANDSCAPE,PaperSize.A4,0,0);
//sheet.addRowPageBreak(12);
jxl.SheetSettings sheetset=sheet.getSettings();
sheetset.setProtected(false);

//sheet.setColumnView(0,5);
//sheet.setColumnView(1,12);

/**************设置单元格字体***************/
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL,10);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);


/**************以下设置几种格式的单元格*************/
//用于正文居左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); //线条
wcf_left.setVerticalAlignment(VerticalAlignment.TOP); //文字垂直对齐
wcf_left.setAlignment(Alignment.LEFT); //文字水平对齐
wcf_left.setWrap(false); //文字是否换行

//用于正文居右
WritableCellFormat wcf_right = new WritableCellFormat(NormalFont);
wcf_right.setBorder(Border.ALL, BorderLineStyle.THIN); //线条
wcf_right.setVerticalAlignment(VerticalAlignment.CENTRE); //文字垂直对齐
wcf_right.setAlignment(Alignment.RIGHT); //文字水平对齐
wcf_right.setWrap(false); //文字是否换行

//用于正文居中
WritableCellFormat wcf_center = new WritableCellFormat(NormalFont);
wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); //线条
wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); //文字垂直对齐
wcf_center.setAlignment(Alignment.CENTRE); //文字水平对齐
wcf_center.setWrap(false); //文字是否换行

//用于跨行
WritableCellFormat wcf_merge = new WritableCellFormat(BoldFont);
wcf_merge.setBorder(Border.NONE, BorderLineStyle.THICK); //线条
wcf_merge.setVerticalAlignment(VerticalAlignment.TOP); //文字垂直对齐
wcf_merge.setAlignment(Alignment.CENTRE); //文字水平对齐
wcf_merge.setWrap(true); //文字是否换行

/**************单元格格式设置完成*******************/
//在excel中加图片
WritableSheet imgsheet = workbook.createSheet("image", 4);
String pathname = "E://java//lib//jexcel2.6//resources//wealdanddownland.png";
WritableImage img = new WritableImage(2, 2, 2.5, 2.5, new File(pathname));

---

/*****************以下是定单内容**********************/
//第0列为项目
//第1列

Vector print=(Vector)session.getAttribute("aa");
String[][] temp=(String[][])print.get(0);
String vBT=(String)print.get(1);
String vXM=(String)print.get(2);
int chang=Integer.parseInt(print.get(3).toString());
java.util.Date systempDate = new java.util.Date();
java.text.SimpleDateFormat format = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String sysDate = format.format(systempDate);
sheet.mergeCells(0,0,chang,2);
sheet.addCell(new Label(0,0,vBT,wcf_merge));
sheet.mergeCells(0,3,chang,3);
sheet.addCell(new Label(0,3,"打印操作员："+vXM,wcf_left));
sheet.mergeCells(0,4,chang,4);
sheet.addCell(new Label(0,4,"打印时　间："+sysDate,wcf_left));
for(int m=0;m {
for(int n=0;n {
sheet.addCell(new Label(n,m+6,temp[m][n].replaceAll("/"","").trim(),wcf_center)); //第0行
}
}

/************以上所写的内容都是写在缓存中的，下一句将缓存的内容写到文件中*********/
workbook.write();
/***********关闭文件**************/
workbook.close();
bExportSucc = true;
}catch(Exception e){
e.printStackTrace();
sErrorStr = e.toString();
System.out.println("在输出到EXCEL的过程中出现错误，错误原因："+e.toString());
}


out.println("");
out.println("<head>");
out.println("");
out.println("</head><body>
");
out.println("

");
if(bExportSucc){
out.println("");
}else{
out.println("在输出到EXCEL文件的过程中发生错误，错误原因："+sErrorStr+"");
}
out.println("

</body>");

}
//Clean up resources
public void destroy() {
}
}

---