Java, ExtJS Export data from the database to Excel and download to the local data backup method-use Servlet__oracle

Recommended this way, because the encapsulation is better, when you need to export the table columns need to change, just need to change in the JS file, not like the action method needs to change the Java code in action.

Each time you click the Export button, call the Exportexcel function and pass in the filename, columnitemsstr, SQL three parameters, which completes the function of the Excel file export to the client. Incoming parameters need to pay attention to ExtJS pass Chinese parameters to the background garbled processing. This article has done the processing, specifically: In JS to Chinese parameters for two times encoding processing, such as: var fileName = "Optical fiber segment"; var url = ' exportexcel ' + ' filename= ' + encodeURI (encodeURI (fileName)) + ' &columnitemsstr= ' + encodeURI (encodeURI (CO LUMNITEMSSTR)) + ' &sql= ' + SQL;, when the background receives the parameter, to decode: String fileName = Urldecoder.decode (request.getparameter ("filename" , "UTF-8"), so that the Chinese parameters can be obtained in the background.

servlet file content (export Excel file feature encapsulation):

Package com.otdrmsys.servlet;
Import java.io.FileNotFoundException;
Import java.io.IOException;
Import Java.io.OutputStream;
Import Java.net.URLDecoder;
Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;

Import java.sql.SQLException;
Import javax.servlet.ServletException;
Import Javax.servlet.http.HttpServlet;
Import Javax.servlet.http.HttpServletRequest;

Import Javax.servlet.http.HttpServletResponse;
Import Org.apache.log4j.Logger;
Import Org.apache.poi.hssf.usermodel.HSSFCell;
Import Org.apache.poi.hssf.usermodel.HSSFRow;
Import Org.apache.poi.hssf.usermodel.HSSFSheet;

Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;

Import Com.otdrmsys.util.DB; /** * Servlet Implementation Class Exportexcel */public class Exportexcel extends HttpServlet {private static final L
	Ong serialversionuid = 1L;
	public static final Logger Errorlogger = Logger.getlogger ("Dailyerror"); 
	
public static final Logger Infologger = Logger.getlogger ("Dailyinfo");    /** * @see httpservlet#httpservlet () * * * Public exportexcel () {super (); }/** */protected void doget (HttpServletRequest request, httpservletresponse response) throws Servletexception, Ioex
		ception {String filename = Urldecoder.decode (Request.getparameter ("FileName"), "UTF-8");
		String columnitemsstr = Urldecoder.decode (Request.getparameter ("Columnitemsstr"), "UTF-8");
		String sql = request.getparameter ("SQL");
		string[] ColumnItems = Columnitemsstr.split (",");//Get column array group Connection conn = Db.createconn () from column names in string form;
		PreparedStatement PS = db.prepare (conn, SQL);
		ResultSet rs = null;
		try {rs = ps.executequery (SQL);
		catch (SQLException e) {e.printstacktrace ();
		} Hssfworkbook Workbook = new Hssfworkbook ();
			try {hssfsheet sheet = workbook.createsheet ();  
	        Createtag (ColumnItems, sheet);//write table column name CreateValue (RS, sheet);//Get data set, then get data, write file//save data in Excel format by response 
	        Response.reset (); Response.setconTenttype ("Application/msexcel;charset=utf-8"); Response.setheader ("Content-disposition", "attachment;filename=\" "+ new String (filename +". xls "). GetBytes (
	        "GBK"), "iso8859_1") + "" ");  
            OutputStream outputstream = Response.getoutputstream (); Workbook.write (OutputStream);  
            Output file to client Outputstream.flush ();  
		Outputstream.close ();
		catch (FileNotFoundException e) {e.printstacktrace ();
		catch (IOException e) {e.printstacktrace ();
		} db.close (PS);
	Db.close (conn); /** * @see Httpservlet#dopost (httpservletrequest request, httpservletresponse response) * * protected void DoPost ( 
	 HttpServletRequest request, HttpServletResponse response) throws Servletexception, IOException {}/** * CREATE TABLE header * * @param tags * * @param sheet * * private static void Createtag (string[] tags, hssfsheet sheet) {Hssfrow row = she
		Et.createrow (0);
		Hssfcell cell = null; Define cell As String type for (int i = 0; i< Tags.length;
			i++) {cell = Row.createcell (i);
Cell.setcellvalue (Tags[i]); 
	Cell.setcelltype (HSSFCELL.ENCODING_UTF_16)//Chinese Processing}}/** * Set table contents * * @param res * @param sheet * *
			private static void CreateValue (Java.sql.ResultSet res, hssfsheet sheet) {try {int flag = 1;
			int count = Res.getmetadata (). getColumnCount ();
			Hssfrow row = null;
			Hssfcell cell = null;
				while (Res.next ()) {row = Sheet.createrow (flag);
					for (int i = 1; I <= count; i++) {cell = Row.createcell (i-1);
					Object obj = Res.getobject (i);
				Cell.setcellvalue (obj + "");
			} flag++;
		} catch (SQLException e) {e.printstacktrace ();
 }
	}
}

DB Encapsulation:

Package com.otdrmsys.util;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import java.sql.Statement;
		public class DB {public static Connection Createconn () {Connection conn = null;
			try {class.forname ("com.mysql.jdbc.Driver");
		conn = Drivermanager.getconnection ("jdbc:mysql://localhost:3306/wenling_map_db", "root", "hel610");
		catch (ClassNotFoundException e) {e.printstacktrace ();
		catch (SQLException e) {e.printstacktrace ();
	Return conn;
		public static PreparedStatement prepare (Connection conn, String sql) {PreparedStatement PS = null;
		try {PS = conn.preparestatement (SQL);
		catch (SQLException e) {e.printstacktrace ();
	} return PS;
			public static void Close (Connection conn) {try {conn.close ();
		conn = null;
		catch (SQLException e) {e.printstacktrace (); }} public static void Close (Statement stmt) {
		try {stmt.close ();
		stmt = null;
		catch (SQLException e) {e.printstacktrace ();
			} public static void Close (ResultSet rs) {try {rs.close ();
		rs = null;
		catch (SQLException e) {e.printstacktrace ();
 }
	}
}

ExtJS The calling statement in code:

{
	ID: ' Fiberlinebackupbtnid ',
	text: ' Export ',
	hidden:true,
	tooltip: ' Back up fiber segment information ',
	iconcls: ' Save ',
	handler:function () {
		var filename = "fibre segment";//filename, without path, without. xls suffix
		var columnitemsstr = "Fiber segment ID, fiber segment name, starting point, end point, Person in charge, cell phone number ";
		var sql = "Select ID, Name, ("
				+ "select name from _station s where s.id = f.startpoint_id), ("
				+ "select name F Rom _station s where s.id = f.endpoint_id), ("
				+" select name from _repairer r where r.id = f.repairer_id), ("
  + "Select Phonenum from _repairer r where r.id = f.repairer_id)"
				+ "from _fiberline f;";
		var url = ' exportexcel ' + ' filename= ' + encodeURI (encodeURI (fileName)) + ' &columnitemsstr= ' + encodeURI (encodeURI (CO LUMNITEMSSTR) + ' &sql= ' + SQL;
		window.location.href = URL;
	}

The implementation effect is as follows:

The contents of the downloaded file: