数据库用DataSet导出到Excel

来源：互联网发布：软件开发编码规范编辑：程序博客网时间：2024/05/21 13:58

ashx页面：

public class Handler1 : IHttpHandler
{

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "application/x-excel";
            string filename = HttpUtility.UrlEncode("用户数据.xls");
            context.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
            string strcon = @"server=192.168.0.130;database=ATP_SEA;uid=sa;pwd=";
            SqlConnection conn = new SqlConnection(strcon);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet();
            BOOKINGTableAdapter adapter = new BOOKINGTableAdapter();
            BOOKDataSet.BOOKINGDataTable data = adapter.GetData();

            HSSFRow dataRow = sheet.CreateRow(0);
            foreach (DataColumn column in data.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }

            for (int i = 0; i < data.Rows.Count&&i<200; i++)
            {
                dataRow = sheet.CreateRow(i + 1);
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    dataRow.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                }
            }
            workbook.Write(context.Response.OutputStream);

        /*错误
            for (int i = 0; i < data.Rows.Count; i++)
            {
                HSSFRow hsRow = sheet.CreateRow(i);
                DBToExcel.BOOKDataSet.BOOKINGRow row = data[i];
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    hsRow.CreateCell(j, HSSFCell.CELL_TYPE_STRING).SetCellValue;
                }
            }
            */        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

html页面：

   <div>
    <a href="bookingToExcel.ashx">导出BOOKING数据到Excel</a>
    </div>