none
listview导出到excel时出错 RRS feed

  • 问题

  • 一段把listview的内容导出到excel时出错,之前能用,在其他电脑应该也能用

    代码在最后,谢谢!

    Private Function ListViewToExcel1(ByVal fileName As String) As Integer If File.Exists(fileName) Then Dim R As Microsoft.VisualBasic.MsgBoxResult = MsgBox("文件:" & fileName & "已经存在!" & vbCr & _ "如果想替换原文件请按""是(YES)"",否则请按""否(NO)""", MsgBoxStyle.YesNo) If R = MsgBoxResult.No Then Return -1 End If Dim excelApp = New Excel.Application Dim excelWorkbook As Excel.Workbook = Nothing Dim excelSheet As Excel.Worksheet = Nothing Try ToolStripStatusLabel1.Text = "正在导出汇总表..." excelApp.Visible = False excelApp.DisplayAlerts = False Dim misValue As Object = System.Reflection.Missing.Value excelWorkbook = excelApp.Workbooks.Add(misValue) excelSheet = CType(excelWorkbook.Worksheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet) excelSheet.Name = "优化结果" Dim rng As Excel.Range rng = excelSheet.Range("A:A") With rng .ColumnWidth = 4.65 .RowHeight = 20 .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter End With rng = excelSheet.Range("B:B") With rng .ColumnWidth = 12.5 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter End With rng = excelSheet.Range("C:C") With rng .ColumnWidth = 8.38 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter End With rng = excelSheet.Range("D:D") With rng .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter .ColumnWidth = 8.38 End With rng = excelSheet.Range("E:E") With rng .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter .ColumnWidth = 4.63 End With rng = excelSheet.Range("F:F") With rng .ColumnWidth = 6.5 End With rng = excelSheet.Range("G:G") With rng .ColumnWidth = 8.38 End With rng = excelSheet.Range("H:H") With rng .ColumnWidth = 8.38 End With rng = excelSheet.Range("I:I") With rng .ColumnWidth = 17.88 End With rng = excelSheet.Range("A1:I1") With rng .Merge() .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter .RowHeight = 50 With .Font .Size = 16 .Bold = True End With .Value = "铝合金型材订货单" End With rng = excelSheet.Range("A2:I2") With rng .Merge() .Value = Now.ToLongDateString() .HorizontalAlignment = Excel.XlHAlign.xlHAlignRight End With For i = 0 To ListView1.Columns.Count - 2 excelSheet.Cells(3, i + 1) = ListView1.Columns(i).Text Next excelSheet.Cells(3, 8) = "表面处理" excelSheet.Cells(3, 9) = "备注" Dim rngStr As String ToolStripProgressBar1.Maximum = ListView1.Items.Count ToolStripProgressBar1.Value = 0 For i = 0 To ListView1.Items.Count - 1 For j = 0 To ListView1.Columns.Count - 2 excelSheet.Cells(i + 4, 1) = ListView1.Items(i).Text excelSheet.Cells(i + 4, j + 1) = ListView1.Items(i).SubItems(j).Text Dim R As Integer = i + 4 rngStr = "G" & (R).ToString & ":G" & (R).ToString rng = excelSheet.Range(rngStr) rngStr = "=D" & R.ToString & "*E" & R.ToString & "*F" & R.ToString & "/1000" rng.Formula = rngStr Next Dim cd As String = ListView1.Items(i).SubItems(3).Text Dim sl As String = ListView1.Items(i).Tag.ToString.Split(","c)(2) If CInt(sl) > 0 Then excelSheet.Cells(i + 4, 9) = "【库存数量:" & sl.ToString & "根】" End If ToolStripProgressBar1.Value += 1 Next excelSheet.Cells(ListView1.Items.Count + 4, 1) = "合计:" rngStr = "G" & (ListView1.Items.Count + 4).ToString & ":G" & (ListView1.Items.Count + 4).ToString rng = excelSheet.Range(rngStr) rng.Formula = "=SUM(G4:G" & (ListView1.Items.Count + 3).ToString & ")" rng = excelSheet.Range("3:3") With rng .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter End With excelSheet.Columns("A:I").EntireColumn.AutoFit() rngStr = "A" & (ListView1.Items.Count + 4).ToString & ":H" & (ListView1.Items.Count + 4).ToString rng = excelSheet.Range(rngStr) rng.Font.Bold = True rngStr = "A3:I" & (ListView1.Items.Count + 4).ToString rng = excelSheet.Range(rngStr) With rng.Borders(Excel.XlBordersIndex.xlInsideVertical) .LineStyle = Excel.XlLineStyle.xlContinuous End With With rng.Borders(Excel.XlBordersIndex.xlInsideHorizontal) .LineStyle = Excel.XlLineStyle.xlContinuous End With With rng.Borders(Excel.XlBordersIndex.xlEdgeTop) .LineStyle = Excel.XlLineStyle.xlContinuous End With With rng.Borders(Excel.XlBordersIndex.xlEdgeBottom) .LineStyle = Excel.XlLineStyle.xlContinuous End With With rng.Borders(Excel.XlBordersIndex.xlEdgeLeft) .LineStyle = Excel.XlLineStyle.xlContinuous End With With rng.Borders(Excel.XlBordersIndex.xlEdgeRight) .LineStyle = Excel.XlLineStyle.xlContinuous End With With excelSheet.PageSetup .PrintTitleRows = "$1:$2" .RightFooter = "第&P页,共&N页" .LeftMargin = excelSheet.Application.InchesToPoints(0.984251968503937) .RightMargin = excelSheet.Application.InchesToPoints(0.590551181102362) .TopMargin = excelSheet.Application.InchesToPoints(0.748031496062992) .BottomMargin = excelSheet.Application.InchesToPoints(0.748031496062992) .HeaderMargin = excelSheet.Application.InchesToPoints(0.31496062992126) .FooterMargin = excelSheet.Application.InchesToPoints(0.31496062992126) End With excelSheet = CType(excelWorkbook.Worksheets.Item(2), Microsoft.Office.Interop.Excel.Worksheet) excelSheet.Name = "切割方式" Const xlRowHeight As Integer = 20 rng = excelSheet.Range("A:A") With rng .RowHeight = xlRowHeight .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter .ColumnWidth = 4.65 End With rng = excelSheet.Range("B:B") With rng .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter .ColumnWidth = 8.38 End With rng = excelSheet.Range("C:C") With rng .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter .ColumnWidth = 11 End With rng = excelSheet.Range("D:E") With rng .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter .ColumnWidth = 8.38 End With rng = excelSheet.Range("F:F") With rng .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter .ColumnWidth = 35.5 End With rng = excelSheet.Range("G:G") With rng .HorizontalAlignment = Excel.XlHAlign.xlHAlignRight .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter .ColumnWidth = 6.5 End With Dim xlRow As Integer = 0 xlRow += 1 rngStr = "A" & (xlRow).ToString & ":" & "G" & (xlRow).ToString Dim rng1 As Excel.Range = excelSheet.Range(rngStr) With rng1 .Merge() .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter .VerticalAlignment = Excel.XlVAlign.xlVAlignTop .RowHeight = xlRowHeight + 10 .Value = "型材切割单" With .Font .Bold = True .Size = 16 End With End With rng = excelSheet.Range("A2:G2") With rng .Merge() .Value = Now.ToLongDateString() .HorizontalAlignment = Excel.XlHAlign.xlHAlignRight End With xlRow += 1 ToolStripProgressBar1.Maximum = ListView1.Items.Count ToolStripProgressBar1.Value = 0 Dim codeS As New List(Of String) For i = 0 To ListView1.Items.Count - 1 codeS.Add(ListView1.Items(i).SubItems(2).Text) Next codeS = (From code In codeS Select code Distinct).ToList For i0 As Integer = 0 To codeS.Count - 1 ToolStripStatusLabel1.Text = "正在导出型材" & ListView1.Items(i0).SubItems(2).Text.Trim & "的切割方式..." xlRow += 1 rngStr = "A" & (xlRow).ToString & ":" & "G" & (xlRow).ToString rng1 = excelSheet.Range(rngStr) With rng1 .Merge() .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .Value = (i0 + 1).ToString & "、型材名称:" & ListView1.Items(i0).SubItems(1).Text.Trim & _ " 型材代号:" & ListView1.Items(i0).SubItems(2).Text.Trim .Font.Bold = True End With xlRow += 1 rngStr = "A" & (xlRow).ToString & ":" & "G" & (xlRow).ToString rng1 = excelSheet.Range(rngStr) Dim x As Integer = 0 Dim ylStrBd As New StringBuilder For i = 0 To ListView1.Items.Count - 1 If ListView1.Items(i).SubItems(2).Text = codeS(i0) Then x += 1 Dim l1 As Integer = 5 Dim l2 As Integer = 2 Dim cd As String = ListView1.Items(i).SubItems(3).Text Dim jk As String = ListView1.Items(i).Tag.ToString.Split(","c)(0) Dim lt As String = ListView1.Items(i).Tag.ToString.Split(","c)(1) Dim ylkc As String = ListView1.Items(i).Tag.ToString.Split(","c)(2) ylStrBd.Append("原料") ylStrBd.Append(x.ToString) ylStrBd.Append(":") ylStrBd.Append(cd.PadLeft(l1)) ylStrBd.Append("(mm)*") ylStrBd.Append(ListView1.Items(i).SubItems(4).Text.PadLeft(4)) ylStrBd.Append("根【" & "库存数量:") ylStrBd.Append(ylkc.ToString.PadLeft(l2)) ylStrBd.Append(" 根】 利用率:") ylStrBd.Append(ListView1.Items(i).SubItems(7).Text) ylStrBd.Append(" (锯口宽:") ylStrBd.Append(jk) ylStrBd.Append(", ") ylStrBd.Append("两端去料头:") ylStrBd.Append(lt) ylStrBd.Append(")") ylStrBd.Append(Chr(10)) End If Next ylStrBd.Remove(ylStrBd.Length - 1, 1) With rng1 .Merge() .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .Value = ylStrBd.ToString .RowHeight = x * 15 End With Dim dh As String = codeS(i0) Dim XC As List(Of 工件) = (From item In 型材表 Where item.代号 = dh Select item).ToList Dim gj As New StringBuilder("规格与数量(长度*数量):" & Chr(10)) For j = 0 To XC.Count - 1 Dim str1 As String = XC(j).长度.ToString & "*" & XC(j).数量.ToString gj.Append(str1.PadRight(12)) Next xlRow += 1 rngStr = "A" & (xlRow).ToString & ":" & "G" & (xlRow).ToString rng1 = excelSheet.Range(rngStr) With rng1 .WrapText = True .RowHeight = ((CInt(Fix(XC.Count / 7)) + 2)) * 15 + 5 .Value = gj.ToString.TrimEnd(Nothing) .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft .VerticalAlignment = Excel.XlVAlign.xlVAlignTop .Merge() End With xlRow += 1 For j = 0 To ListView3.Columns.Count - 1 excelSheet.Cells(xlRow, j + 1) = ListView3.Columns(j).Text Next ' Dim L As Integer = 0 For j = 0 To ListView3.Items.Count - 1 If ListView3.Items(j).SubItems(2).Text = dh Then L += 1 For k = 0 To ListView3.Columns.Count - 1 excelSheet.Cells(xlRow + L, 1) = ListView3.Items(j).Text.Trim excelSheet.Cells(xlRow + L, k + 1) = ListView3.Items(j).SubItems(k).Text.TrimEnd(Nothing) Next End If Next rngStr = "A" & (xlRow).ToString & ":" & "G" & (xlRow + L).ToString rng1 = excelSheet.Range(rngStr) With rng1 .RowHeight = xlRowHeight With .Borders(Excel.XlBordersIndex.xlInsideVertical) .LineStyle = Excel.XlLineStyle.xlContinuous End With With .Borders(Excel.XlBordersIndex.xlInsideHorizontal) .LineStyle = Excel.XlLineStyle.xlContinuous End With With .Borders(Excel.XlBordersIndex.xlEdgeTop) .LineStyle = Excel.XlLineStyle.xlContinuous End With End With xlRow += L rngStr = "A" & (xlRow).ToString & ":" & "G" & (xlRow - L - 2).ToString rng1 = excelSheet.Range(rngStr) With rng1 With .Borders(Excel.XlBordersIndex.xlEdgeTop) .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlMedium End With With .Borders(Excel.XlBordersIndex.xlEdgeBottom) .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlMedium End With With .Borders(Excel.XlBordersIndex.xlEdgeLeft) .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlMedium End With With .Borders(Excel.XlBordersIndex.xlEdgeRight) .LineStyle = Excel.XlLineStyle.xlContinuous .Weight = Excel.XlBorderWeight.xlMedium End With End With xlRow += 1 rngStr = "A" & (xlRow).ToString & ":" & "G" & (xlRow).ToString rng1 = excelSheet.Range(rngStr) rng1.RowHeight = xlRowHeight + 5 rng = excelSheet.Range("A:G") rng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter ToolStripProgressBar1.Value += 1 Next ''页边距,页脚 With excelSheet.PageSetup .PrintTitleRows = "$1:$2" .RightFooter = "第&P页,共&N页" .LeftMargin = excelSheet.Application.InchesToPoints(0.984251968503937) .RightMargin = excelSheet.Application.InchesToPoints(0.590551181102362) .TopMargin = excelSheet.Application.InchesToPoints(0.748031496062992) .BottomMargin = excelSheet.Application.InchesToPoints(0.748031496062992) .HeaderMargin = excelSheet.Application.InchesToPoints(0.31496062992126) .FooterMargin = excelSheet.Application.InchesToPoints(0.31496062992126) End With Save: excelWorkbook.SaveAs(fileName) rng = Nothing rng1 = Nothing rngStr = Nothing Return 1 Catch ex As Exception ToolStripStatusLabel1.Text = "型材优化单导出失败!" MsgBox(ex.Message) Return -1 Finally excelSheet = Nothing If excelWorkbook IsNot Nothing Then excelWorkbook.Close(False) excelWorkbook = Nothing End If excelApp.Quit() NAR(excelApp) GC.Collect() End Try End Function

    Private Sub NAR(ByVal o As Object)
            Try
                While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
                End While
            Catch
            Finally
                o = Nothing
            End Try
        End Sub



    Work8862


    • 已编辑 Work8862 2016年10月24日 15:31
    2016年10月24日 15:24

全部回复

  • 可能是Excel的版本不一样,参考下“之前能用的电脑的”的Excel版本,在报错的电脑中,更换Excel版本。

    专注于.NET ERP/CRM开发框架,C/S架构,SQL Server + ORM(LLBL Gen Pro) + Infragistics WinForms

    2016年10月25日 1:24
  • 除了Shuanghua的建议,我提议你使用不依赖Office的组件:

    NPOI:http://npoi.codeplex.com/releases/

    具体使用教程:http://wenku.baidu.com/link?url=7ZCuTvyuPAiGbQgm9zhkCH7c0P8LocAKDO60Ln2yDkZRM3I-bUosigCNu_aWR5ylUNGCNhFRtkxSe83Fxm3BDuQQLRF0etDqqhFNsSt2cam


    ASP.NET Forum
    StackOverFlow
    FreeRice Donate
    Issues to report



    2016年10月25日 12:32
    版主