怎么将数据库数据导入excel出错 求教高手
public void CreateExcel(DataSet ds)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=filename.xls");
Response.ContentType = "application/ms-excel";
string colHeaders = "", ls_item = "";
DataTable dt = ds.Tables["0"];
DataRow[] myRow = dt.Select();
int i;
int cl = dt.Columns.Count;
for (i = 0; i < cl; i++)
{
if (i == cl - 1)
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
}
resp.Write(colHeaders);
foreach (DataRow row in myRow)
{
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))
{
ls_item += row[i].ToString() + "\t";
}
else
{
ls_item += row[i].ToString() + "\n";
}
}
resp.Write(ls_item);
ls_item = "";
}
resp.End();
}
按钮事件--------------------------------
protected void id1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("server=WWW-37894A7821D;uid=sa;pwd=123;database=COLWAP");
con.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from service_base_question_key",con);
DataSet ds = new DataSet();
da.Fill(ds, "service_base_question_key");
CreateExcel(ds);
}
为什么我点击按钮的时候会提示我“未将对象引用设置到对象的实例” 就是在 DataRow[] myRow = dt.Select();这段代码
求教高手
[解决办法]
//导出excel的公共方法 public void ExportData(DataSet ds, string xlsName) { try { XlsDocument xls = new XlsDocument(); xls.FileName = xlsName; int rowIndex = 1; int colIndex = 0; DataTable table = ds.Tables[0]; Worksheet sheet = xls.Workbook.Worksheets.Add("导出资源");//状态栏标题名称 Cells cells = sheet.Cells; foreach (DataColumn col in table.Columns) { colIndex++; //sheet.Cells.AddValueCell(1,colIndex,col.ColumnName);//添加XLS标题行 cells.Add(1, colIndex, col.ColumnName.ToString()); } foreach (DataRow row in table.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in table.Columns) { colIndex++; //sheet.Cells.AddValueCell(rowIndex, colIndex, row[col.ColumnName].ToString());//将数据添加到xls表格里 //Cell cell= cells.AddValueCell(rowIndex, colIndex, Convert.ToDouble(row[col.ColumnName].ToString()));//转换为数字型 Cell cell = cells.Add(rowIndex, colIndex, row[col.ColumnName].ToString()); //如果你数据库里的数据都是数字的话 最好转换一下，不然导入到Excel里是以字符串形式显示。 //cell.Font.FontFamily = FontFamilies.Roman; //字体 //cell.Font.Bold = true; //字体为粗体 cell.Font.Weight = FontWeight.Normal; } } xls.Save(true); } catch { throw; } }
[解决办法]
//导出EXCEL protected void Button19_Click(object sender, EventArgs e) { GridView1.AllowPaging = false; GridView1.AllowSorting = false; GridView1.DataBind(); ToExcel(GridView1, "aaa.xls"); GridView1.AllowPaging = true; GridView1.AllowSorting = true; GridView1.DataBind(); } public override void VerifyRenderingInServerForm(Control control) { // Confirms that an HtmlForm control is rendered for } private void ToExcel(Control ctl, string FileName) { HttpContext.Current.Response.Charset = "UTF-8"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName); ctl.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End(); }