toexcel.java

这是一个大学生论文管理系统,采用JSP语言进行开发

package com.servlet;

import java.io.*;
import java.net.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.hssf.util.*;
import com.db.CommonBean;
import com.util.DealString;

public class toexcel extends HttpServlet 
{

	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{
		doPost(request,response);
	}
	
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{
	  CommonBean cb = new CommonBean();
	  try{
		  
		DealString ds = new DealString();
		
		//SQL
		String sql = ds.toGBK(request.getParameter("sql"));
		sql = sql.replaceAll("@","%");

		//标题
		String title = ds.toGBK(request.getParameter("title"));

		//表头数组
		String fields = ds.toGBK(request.getParameter("fields"));
		String field_chn[] = ds.splitStr(fields, ',') ;
		
		//字段名数组
		String fields1 = sql.substring(7,sql.length());//去掉select
		fields1 = fields1.substring(0,fields1.indexOf("from")).trim();
		if(fields1.indexOf("distinct")!=-1)//去掉distinct
			fields1 = fields1.substring(9,fields1.length());
		fields1 = fields1.replaceAll("a.","").trim();//去掉a.
		String field_en[] = ds.splitStr(fields1, ',') ;

		//数据库查询记录
		Vector vect = cb.getDataBySql(sql);








			//创建一个工作表----------------------------------------------------(1)
			HSSFWorkbook wb = new HSSFWorkbook();

			//wb.setSheetName(0,"指标表",HSSFWorkbook.ENCODING_UTF_16);
			//System.out.println(name);
			
			//设置字体位置------------------------------------------------------(2)
			HSSFCellStyle style1 = wb.createCellStyle();
			HSSFCellStyle style2 = wb.createCellStyle();
			HSSFCellStyle style3 = wb.createCellStyle();
			style1.setAlignment((short)2);
			style2.setAlignment((short)2);
			style3.setAlignment((short)3);
			//设置字体---------------------------------------------------------(3)
			HSSFFont font1 = wb.createFont();
			HSSFFont font2 = wb.createFont();
			HSSFFont font3 = wb.createFont();

			font1.setFontHeightInPoints((short)11);
			font1.setFontName("宋体");
			//font.setItalic(true);
			//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style1.setFont(font1);

			font2.setFontHeightInPoints((short)11);
			font2.setFontName("宋体");
			//font.setItalic(true);
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style2.setFont(font2);

			font3.setFontHeightInPoints((short)11);
			font3.setFontName("宋体");
			//font3.setItalic(true);
			font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style3.setFont(font3);


			style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
			style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直举重
			style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
			style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
			style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
			style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
			style1.setWrapText(true);//自动换行

			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
			style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直举重
			style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
			style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
			style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
			style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
			style2.setWrapText(true);//自动换行


			style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
			style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直举重
			style3.setWrapText(true);//自动换行

			HSSFSheet sheet = null;//创建表单
			HSSFRow row = null;//行
			HSSFCell cell = null;//单元格





			//创建一个Sheet--------------------------------------------------------(4)
			if(title.equals("")) title = "查询结果" ;
			sheet = wb.createSheet( title );
			sheet.setColumnWidth((short)0,(short)2000);
			for( int i = 0; i < field_chn.length; i++ )
			{			
				sheet.setColumnWidth((short)(i+1),(short)4000);
			}

			//写入数据----------------------------------------------------------------(5)
			row = sheet.createRow((short) 0);
			row.setHeight((short)400);
			cell = row.createCell((short) 0);

			//大的标题------------------------------------------------------------------(6)
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(title);
			cell.setCellStyle(style3);

			//合并单元格----------------------------------------------------------------(7)
			sheet.addMergedRegion(new Region(0,(short)0,0,(short)(field_chn.length)));

			//标题栏--------------------------------------------------------------------(8)
			row = sheet.createRow((short) 1);
			row.setHeight((short)400);
			cell = row.createCell((short)0);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue("序号");
			cell.setCellStyle(style2);
			for (int j=0;j<field_chn.length;j++)
			{
				cell = row.createCell((short)(j+1));
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(field_chn[j]);
				cell.setCellStyle(style2);
			}



			//显示数据-----------------------------------------------------------------(9)
			for (int i=0;i<vect.size();i++ )
			{
				Hashtable hash = (Hashtable)vect.get(i);
				
				row = sheet.createRow((short)(i+2));
				
				cell = row.createCell((short)0);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(i+1);
				cell.setCellStyle(style1);
				
				int r = 1 ;
				for(int j=0; j<field_en.length; j++ )
				{
					if(field_en[j].equals("id"))continue;
					String value = ds.toString((String)hash.get(field_en[j]));

					cell = row.createCell((short)(r++));
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					cell.setCellValue(value);
					cell.setCellStyle(style1);
				}
			}		



		response.setContentType("application/x-download");
		response.setContentType("application/vnd.ms-excel;charset=gb2312");
		response.setHeader("Content-Disposition","attachment;filename="+ds.toUtf8String(title+".xls"));
		OutputStream output = response.getOutputStream();
		wb.write(output);
		output.close();
		
		}catch (Exception e){}
		finally
		{
			if(cb!=null)
				cb.closeConn();
		}
    }
	
	public void  destroy () 
	{
	}
}