Question

VB.NET 导出生成EXCEL，不调用EXCEL组件，如图错误.

代码如下：
'datatable 导出到excel
Public Function toexcel(ByVal table As DataTable) As Boolean
Try

Dim name As String = "客户积分表"

Dim coding As String = "UTF-8"

HttpContext.Current.Response.Clear()
Dim sw As New System.IO.StringWriter
Dim iColCount As Integer = table.Columns.Count
Dim j As Integer = 0
Dim strColumns As String = ""
For i As Integer = 0 To iColCount - 1

strColumns += CStr(table.Columns(i).ColumnName.ToLower).ToString + Chr(9)
Next
sw.WriteLine(strColumns)
For j = 0 To table.Rows.Count - 1
strColumns = ""
For i As Integer = 0 To iColCount - 1

strColumns += CStr(table.Rows(j)(i)).ToString + Chr(9)
Next
sw.WriteLine(strColumns)
Next
sw.Close()
Response.AddHeader("Content-Disposition", "attachment; filename=" + name + ".xls")
Response.ContentType = "application/ms-excel"
Response.ContentEncoding = System.Text.Encoding.Default
EnableViewState = False
Response.Write(sw)
Response.End()

Return True
Catch ex As Exception

Me.lblErrorShow.Text = ex.Message.ToString
End Try

End Function

Answer 1

代码
protected void exportToExcelClient(IDictionary<string, string> map) { if (map.Count == 0) { MessageBox.Show("请选择要导出的字段！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } pgbClient.Value = 0; _excel = new Excel.Application(); Excel.Workbook wbook = _excel.Workbooks.Add(Missing.Value); Excel.Worksheet wsheet = (Excel.Worksheet)_excel.ActiveSheet; Excel.Range range = (Excel.Range)wsheet.Cells; Excel.Range ran = null; string tofilepath = Application.StartupPath + "\\Excel_save\\"; string tofilename = "customer.xls"; string sql = "SELECT "; ArrayList keyArr = new ArrayList(); foreach(KeyValuePair<string,string> entry in map) { sql+=entry.Value+","; keyArr.Add(entry.Key); } sql = sql.Substring(0, sql.Length - 1); sql += " FROM tb_ClientInfo WHERE 1=1"+condition; //MessageBox.Show(sql); DataSet myds = boperate.getds(sql, "tb_Client"); DataTable dt = myds.Tables["tb_Client"]; for (int i = 0; i < keyArr.Count; i++) { ran = (Excel.Range)range[1, i + 1]; ran.Font.Bold = true; ran.Font.Size = 10; ran.Value2 = keyArr[i]; //ran.Columns.AutoFit(); NAR(ran); pgbClient.Step = 10/keyArr.Count; pgbClient.Value += 10 / keyArr.Count; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { ran = (Excel.Range)range[i + 2, j + 1]; ran.Value2 = dt.Rows[i][j]; ran.Font.Size = 10; ran.Columns.AutoFit(); NAR(ran); } pgbClient.Step = 90 / dt.Rows.Count; pgbClient.Value += 90 / dt.Rows.Count; } if (!Directory.Exists(tofilepath)) { Directory.CreateDirectory(tofilepath); } if (File.Exists(tofilepath + tofilename)) { File.Delete(tofilepath + tofilename); } wbook.SaveAs(tofilepath + tofilename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); wbook.Close(false, Type.Missing, Type.Missing); NAR(range); NAR(wsheet); _excel.Quit(); NAR(_excel); pgbClient.Value = pgbClient.Maximum; System.Threading.Thread.Sleep(500); System.Diagnostics.Process.Start("EXCEL.EXE",tofilepath+tofilename); //this.Close(); } /// <summary> /// 释放资源 /// </summary> /// <param name="o"></param> private void NAR(object o) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(o); } catch { } finally { o = null; } }