使用EEPlus从Datatable导出到Excel不起作用 [英] Export to Excel from a Datatable using EEPlus doesn't work
本文介绍了使用EEPlus从Datatable导出到Excel不起作用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Hello Experts,
根据Richard Deeming的评论。我尝试使用EEplus将数据导出到Excel。
这是我的代码
Dim file As FileInfo = New FileInfo(ClaimForms_)
使用pck作为新ExcelPackage(文件)
Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add(Sheet3)
Dim dsExcel As DataSet = dbnet.GetAllDetails()
Dim dt As DataTable = dsExcel.Tables(0)
ws.Cells(A1)。LoadFromDataTable(dt,True)
ws.Cells.AutoFitColumns()
使用rng As ExcelRange = ws.Cells(1,1,1,dt.Columns.Count)
rng.Style.Font.Bold = True
rng.Style.Fill.PatternType = ExcelFillStyle.Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79,129,189))
rng.Style.Font.Color.SetColor(System.Drawing.Color.White)
结束使用
pck.Save()
Response.ContentType =application / vnd.openxmlformats-officedocument.spreadsheetml.sheet
Dim fileName As String = String.Format(ClaimReport_ {0:yyyyMMdd_HH_mm} .xlsx,DateTime.Now)
Dim disposition As String = String.Format(attachment; filename ={0},fileName)
Response.AppendHeader(Content-Disposition,处置)
Response.BinaryWrite(pck.GetAsByteArray())
Response.TransmitFile (file.Name)
Response.Flush()
结束使用
我在下面的行中收到错误
Response.BinaryWrite(pck.GetAsByteArray())
说Part已经存在。当我尝试打开Excel时。它是空的空显示它已损坏
有人可以帮我这个吗?
解决方案
删除pck.Save()
行。
您还需要删除Response.TransmitFile(file.Name)
line - 您已经在上一行发送了该文件。
参考Richard我终于得到了解决方案
这是我的代码:
Dim file As FileInfo = New FileInfo(ClaimForms.xls)
使用pck作为新ExcelPackage(文件)
Dim wsClaims作为ExcelWorksheet = pck.Workbook.Worksheets.Add(Sheet1)
wsClaims.TabColor = Color.Blue
Dim wslineitems作为ExcelWorksheet = pck.Workbook.Worksheets.Add(Sheet2)
wslineitems.TabColor = Color.Blue
Dim dsExcel As DataSet = dbnet.GetAllDetails()
Dim dtClaims As DataTable = dsExcel.Tables(0)
Dim dtLineItems As DataTable = dsExcel.Tables(1)
wsClaims.Cells(A1)。LoadFromDataTable(dtClaims,True)
wslineitems.Cells(A1)。LoadFromDataTable(dtLineItems,True)
wsClaims.Cells.AutoFitColumns()
wslineitems.Cells.AutoFitColumns()
使用rng作为ExcelRange = wsClaims.Cells(1,1,1,dtClaims.Columns.Count)
rng.Style.Font.Bold = True
rng .Style.F ill.PatternType = ExcelFillStyle.Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Blue)
rng.Style.Font.Color.SetColor(System.Drawing.Color.Yellow) )
结束使用
使用rng作为ExcelRange = wslineitems.Cells(1,1,1,dtLineItems.Columns.Count)
rng.Style.Font.Bold = True
rng .Style.Fill.PatternType = ExcelFillStyle.Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Blue)
rng.Style.Font.Color.SetColor(System.Drawing。 Color.Yellow)
结束使用
Dim result As MemoryStream = New MemoryStream
pck.SaveAs(result)
Response.ContentType =application / vnd.openxmlformats-officedocument.spreadsheetml。 sheet
Dim fileName As String = String.Format(ClaimReport_ {0:yyyyMMdd_HH_mm} .xlsx,DateTime.Now)
Dim disposition As String = String.F ORMAT(附件; filename ={0},fileName)
Response.AppendHeader(Content-Disposition,处置)
result.WriteTo(Response.OutputStream)
'Response.BinaryWrite( pck.GetAsByteArray())
'Response.TransmitFile(file.Name)
'Response.Flush()
Response.End()
End using
我删除了从EEplus网站下载的Excelpackage.dll。我添加了名为EEplus.dll的新dll。一切都运行良好。
Hello Experts,
As per the comments given by Richard Deeming. I tried to use EEplus for exporting data to Excel.
Here is my code
Dim file As FileInfo = New FileInfo("ClaimForms_") Using pck As New ExcelPackage(file) Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add("Sheet3") Dim dsExcel As DataSet = dbnet.GetAllDetails() Dim dt As DataTable = dsExcel.Tables(0) ws.Cells("A1").LoadFromDataTable(dt, True) ws.Cells.AutoFitColumns() Using rng As ExcelRange = ws.Cells(1, 1, 1, dt.Columns.Count) rng.Style.Font.Bold = True rng.Style.Fill.PatternType = ExcelFillStyle.Solid rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)) rng.Style.Font.Color.SetColor(System.Drawing.Color.White) End Using pck.Save() Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" Dim fileName As String = String.Format("ClaimReport_{0:yyyyMMdd_HH_mm}.xlsx", DateTime.Now) Dim disposition As String = String.Format("attachment; filename=""{0}""", fileName) Response.AppendHeader("Content-Disposition", disposition) Response.BinaryWrite(pck.GetAsByteArray()) Response.TransmitFile(file.Name) Response.Flush() End Using
I get errors at the below line
Response.BinaryWrite(pck.GetAsByteArray())
saying that Part already exists. Also when I try to open the Excel. It is blank empty showing that it is corrupted
could someone help me with this please?
解决方案
Remove thepck.Save()
line.
You'll also need to remove theResponse.TransmitFile(file.Name)
line - you've already sent the file on the previous line.
With reference to Richard deeming solution 1. I finally got solution
Here is my Code:
Dim file As FileInfo = New FileInfo("ClaimForms.xls") Using pck As New ExcelPackage(file) Dim wsClaims As ExcelWorksheet = pck.Workbook.Worksheets.Add("Sheet1") wsClaims.TabColor = Color.Blue Dim wslineitems As ExcelWorksheet = pck.Workbook.Worksheets.Add("Sheet2") wslineitems.TabColor = Color.Blue Dim dsExcel As DataSet = dbnet.GetAllDetails() Dim dtClaims As DataTable = dsExcel.Tables(0) Dim dtLineItems As DataTable = dsExcel.Tables(1) wsClaims.Cells("A1").LoadFromDataTable(dtClaims, True) wslineitems.Cells("A1").LoadFromDataTable(dtLineItems, True) wsClaims.Cells.AutoFitColumns() wslineitems.Cells.AutoFitColumns() Using rng As ExcelRange = wsClaims.Cells(1, 1, 1, dtClaims.Columns.Count) rng.Style.Font.Bold = True rng.Style.Fill.PatternType = ExcelFillStyle.Solid rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Blue) rng.Style.Font.Color.SetColor(System.Drawing.Color.Yellow) End Using Using rng As ExcelRange = wslineitems.Cells(1, 1, 1, dtLineItems.Columns.Count) rng.Style.Font.Bold = True rng.Style.Fill.PatternType = ExcelFillStyle.Solid rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Blue) rng.Style.Font.Color.SetColor(System.Drawing.Color.Yellow) End Using Dim result As MemoryStream = New MemoryStream pck.SaveAs(result) Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" Dim fileName As String = String.Format("ClaimReport_{0:yyyyMMdd_HH_mm}.xlsx", DateTime.Now) Dim disposition As String = String.Format("attachment; filename=""{0}""", fileName) Response.AppendHeader("Content-Disposition", disposition) result.WriteTo(Response.OutputStream) 'Response.BinaryWrite(pck.GetAsByteArray()) 'Response.TransmitFile(file.Name) ' Response.Flush() Response.End() End Using
I deleted the Excelpackage.dll which I downloaded from EEplus website. I added new dll called EEplus.dll. Everything works perfectly well.
这篇关于使用EEPlus从Datatable导出到Excel不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文