spring MVC 中生成excel

为什么80%的码农都做不了架构师？>>>   

    根据从前台传入的参数（param - X）、参数（tips_X_user,X为参数个数）个数查出对应的sql语句，生成结果集（ResultSet），再根据结果集生成excel。

控制类：ViewController

package org.tips.ctr;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.multiaction.MultiActionController;
import org.tips.dao.DBHelper;
import org.tips.util.GetDBConfig;
import org.tips.util.ViewExcel;
/***
 * 导出excel控制通用类
 * @author tips
 *参数：param -X
 */

public class ViewController extends MultiActionController {

	/***set连接池
	 * <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" lazy-init="false">
	 */
	private BasicDataSource dataSource;

	public BasicDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(BasicDataSource dataSource) {
		this.dataSource = dataSource;
	}

	// 操作excel
	public ModelAndView viewExcel(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		List<String> params = new ArrayList<String>();// 保存条件参数
		/***
		 * indexSQL的格式为 tips_2_user,2代表2个条件参数
		 * 例如两个参数从前台传过来，param0=scott && param1=tiger
		 * 分别代表用户名和密码
		 */
		String indexSQL = request.getParameter("indexSQL");
		int i = 0;
		if (indexSQL != null && indexSQL.length() > 1) {
			StringBuffer sbf = new StringBuffer(indexSQL);
			Pattern pattern = Pattern.compile("_");
			String[] str = pattern.split(sbf);
			String index = str[1];
			i = Integer.parseInt(index);
		}

		//将页面传入的条件参数保存到ArrayList中
		for (int j = 0; j < i; j++) {
			String param = request.getParameter("param" + j);
			params.add(param);
		}

		/***
		 * 从属性文件中获取sql语句（param0，param1）
		 * indexSQL = select uname,pwd,sex,email from test_users where uname=? and pwd = ?
		 */
		String sql = GetDBConfig.getSQL(indexSQL);
		Connection conn = dataSource.getConnection();

		DBHelper dbHelper = new DBHelper(conn);//数据增删查改通用类
		ResultSet rs = dbHelper.RsBySql(sql, params);

		//此处将conn传入想在里面关闭连接，在该类中关闭，则rs会等于null
		ViewExcel viewExcel = new ViewExcel(conn, rs);

		return new ModelAndView(viewExcel);
	}

}

excel生成类：ViewExcel。

package org.tips.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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 org.springframework.web.servlet.view.document.AbstractExcelView;

public class ViewExcel extends AbstractExcelView {

	// 将数据集导出excel
	private Connection conn;
	private ResultSet rs;

	public ViewExcel() {

	}

	public ViewExcel(Connection conn, ResultSet rs) {
		this.conn = conn;
		this.rs = rs;
	}

	@Override
	protected void buildExcelDocument(Map arg0, HSSFWorkbook arg1,
			HttpServletRequest arg2, HttpServletResponse arg3) throws Exception {
		// TODO Auto-generated method stub

		HSSFSheet sheet = arg1.createSheet("list");
		sheet.setDefaultColumnWidth(12);//表格宽度
		if (rs == null) {
			HSSFCell cell = getCell(sheet, 0, 0);
			setText(cell, "No data");
		} else {
			ResultSetMetaData rsmd = rs.getMetaData();
			// 存储全部的列名
			String[] columnname = new String[rsmd.getColumnCount()];
			for (int i = 0; i < columnname.length; i++) {
				columnname[i] = rsmd.getColumnName(i + 1);
			}

			//设置excel列名
			for (int i = 0; i <= columnname.length; i++) {
				if (i != 0) {
					HSSFCell cell = getCell(sheet, 0, i);
					setText(cell, columnname[i-1]);
				} else {
					HSSFCell cell = getCell(sheet, 0, 0);
					setText(cell, "ROW");
				}
			}

			// 导数据集
			int row = 1;
			while (rs.next()) {
				HSSFRow sheetRow = sheet.createRow(row);
				sheetRow.createCell(0).setCellValue(row);
				for (int i = 1; i <= columnname.length; i++) {
					String tempValue = String.valueOf(rs.getObject(i));
					sheetRow.createCell(i).setCellValue(tempValue);
				}
				row++;
			}
		}
		// 关闭数据集
		if (rs != null) {
			rs.close();
		}
		if (conn != null) {
			conn.rollback();
			conn.close();
		}

	}

}

读取SQL语句类：GetDBConfig

package org.tips.util;

import java.io.IOException;
import java.util.Properties;
/***
 * 获取属性文件中sql语句
 * @author tips
 *
 */
public class GetDBConfig {

	private final static String CONFIG_PATH = "org\\tips_sql.properties";
	
	public static String getSQL(String index){
		String sql = "";
		Properties proper = new Properties();
		try {
			proper.load(GetDBConfig.class.getClassLoader().getResourceAsStream(CONFIG_PATH));
			sql = proper.getProperty(index);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println("找不到配置文件路径！");
			e.printStackTrace();
		}
		return sql;
	}
}

注意：在web.xml中需配置*.xls，这个请求在打开的保存提示中生成的文件扩展名是.xls，否则保存时需手动改成.xls

<servlet>
		<servlet-name>tips</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>

	<servlet-mapping>
		<servlet-name>tips</servlet-name>
		<url-pattern>*.do</url-pattern>
		<url-pattern>*.xls</url-pattern>
	</servlet-mapping>

前台body：

<a href="viewExcel.xls?indexSQL=tips_2_user&&param0=scott&&param1=tiger">tips_2_user</a>

tips_servlet.xml配置：

<!-- 操作excel -->
	<bean id="viewController" class="org.tips.ctr.ViewController">
		<property name="dataSource">
			<ref bean="dataSource" />
		</property>
	</bean>

在org\tips_sql.properties文件中添加：tips_2_user = select uname,pwd,sex,email from test_users where uname=? and pwd = ?

OK，这样我们就只要关注前台传递的参数和tips_sql.properties文件中的sql就可以了。

--

--

 

 

 

转载于:https://my.oschina.net/scotts/blog/75501