jxl导入数据到EXCEL
1.发送指令jsp页面
<html> <head> <meta http-equiv="Content-type" content="text/html; charset=utf-8"> <title>Alarm Report</title> <script type="text/javascript"> function ajaxSubmit(){ window.open("/rmcs/alarmOutputToExcel.jsp",'', 'menubar=yes, location=yes, toolbar=yes, status=no, titlebar=no, resizable=yes, height=430px, width=650px, top='+(screen.height-430)/2+'px, left='+(screen.width-650)/2+'px'); } </script> </head> <body> <input type="button" onclick="ajaxSubmit()" value="button" /> </body> </html>
2.请求后台处理页面
<%@ page contentType="text/html; charset=UTF-8" %> <%@ page import="servlet.CASAlertReport"%> <% try { CASAlertReport.outputExcel(request, response); } catch (Exception e) { e.printStackTrace(); } %>
3.后台处理页面
package servlet;
/**数据库应用包*/
import com.db.Database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**servlet应用包*/
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**jxl应用包*/
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class CASAlertReport {
public static void outputExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
WritableWorkbook wwb = null;
try{
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
/**设置导出报表的文件名称*/
response.setHeader("Content-disposition","attachment; filename=Alarm Report - By Device.xls");
WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setEncoding("iso-8859-1");
wwb = Workbook.createWorkbook(response.getOutputStream(), workbookSettings);
wwb.createSheet("Alarm Report", 10);
WritableSheet wsSummary = wwb.getSheet("Alarm Report");
/**设置报表内容的字体样式*/
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
wsSummary.setRowView(1, 500);
// Report Title
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setAlignment(Alignment.LEFT);
WritableCellFormat wcf_r = new WritableCellFormat(wf);
wcf_r.setAlignment(Alignment.RIGHT);
wsSummary.setColumnView(0, 30);
wsSummary.setColumnView(1, 30);
wsSummary.setColumnView(2, 30);
wsSummary.setColumnView(3, 30);
int rw = 7;
wsSummary.addCell(new Label(0, rw - 1, "Severity", wcf));
wsSummary.addCell(new Label(1, rw - 1, "Platform", wcf));
wsSummary.addCell(new Label(2, rw - 1, "No. of Acked Alarms", wcf_r));
wsSummary.addCell(new Label(3, rw - 1, "No. of Unacked Alarms", wcf_r));
rw++;
Database db = new Database();
String sql = "select * from vehicle";
ResultSet rs = db.getResultSet(sql);
while (rs.next()) {
wsSummary.addCell(new Label(0, rw - 1, rs.getString("license"), wcf));
wsSummary.addCell(new Label(1, rw - 1, rs.getString("IMEI"), wcf));
wsSummary.addCell(new Label(2, rw - 1, rs.getString("color"), wcf));
wsSummary.addCell(new Label(3, rw - 1, rs.getString("brand"), wcf));
rw ++;
}
wwb.write();
wwb.close();
} catch(Exception e){
e.printStackTrace();
return;
}
}
}
编辑完以上代码，修改一下后台的数据库连接就可以使用啦，很方便很强大的。
