DataSet资料转到Exec的做法
protected void btnExcel_Click(object sender, EventArgs e)
{
if (this.dalListContract.Items.Count > 0)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("序号");
dt.Columns.Add("名称");
if (null != Cache["toExcel"])
{
DataTable temp = (DataTable)Cache["toExcel"];
for (int i = 0; i < temp.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
dr[0] = i + 1;
dr[1] = temp.Rows[i]["code"];
dr[2] = temp.Rows[i]["classne"];
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
ExportExcel(ds, "test");
}
}
}
private void ExportExcel(DataSet ds, string filename)
{
Microsoft.Office.Interop.Excel.Application oExcel;
oExcel = new Microsoft.Office.Interop.Excel.Application();
try
{
Microsoft.Office.Interop.Excel.Workbook oBook;
object oMissing = System.Reflection.Missing.Value;
oBook = oExcel.Workbooks.Add(oMissing);
HttpResponse response = HttpContext.Current.Response;
int lie = ds.Tables[0].Columns.Count;
int hang = ds.Tables[0].Rows.Count;
int i, j, t;
string panduanstring = "";
i = 1;
for (j = 0; j < lie; j++)//标题
{
oExcel.Cells[1, i++] = ds.Tables[0].Columns[j].ColumnName;
}
t = 1;
for (i = 0; i < hang; i++)//内容
{
for (j = 0; j < lie; j++)
{
panduanstring = ds.Tables[0].Rows[i][j].ToString();
if (panduanstring.GetType().ToString() == "System.String")
{
oExcel.Cells[i + 2, t++] = "'" + ds.Tables[0].Rows[i][j].ToString();
}
else
oExcel.Cells[i + 2, t++] = ds.Tables[0].Rows[i][j].ToString();
}
t = 1;
}
oExcel.Visible = true;
oBook.Saved = true;
oExcel.UserControl = false;
string path = Server.MapPath("excel/");
string mm = path + filename + ".xls";
oExcel.ActiveWorkbook.SaveCopyAs(mm);
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oExcel);
GC.Collect();
response.Redirect("excel/" + filename + ".xls");
}
catch (Exception e)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)oExcel);
GC.Collect();
}
}
|
