vb.net - Microsoft.Office.Interop.Excel - 如何在 vb.net 中将 Excel（仅限可见单元格）保存为 CSV

Question

目前我可以将 Excel 保存为 CSV，如下所示。但是，它保存了包括不可见单元格的所有数据。

Dim excelApplication As Microsoft.Office.Interop.Excel.Application
Dim workbook As Microsoft.Office.Interop.Excel.Workbook
excelApplication = New Microsoft.Office.Interop.Excel.Application
excelApplication.Visible = False
excelApplication.DisplayAlerts = False
workbook = excelApplication.Workbooks.Open(in_InputFilePath)
workbook.SaveAs(in_OutputFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
workbook.Close()
excelApplication.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication)

标签： vb.netexcel-interop

Answer 1

Dim excelApplication As Microsoft.Office.Interop.Excel.Application
Dim workbook1 As Microsoft.Office.Interop.Excel.Workbook
Dim workbook1worksheet1 As Microsoft.Office.Interop.Excel.Worksheet
Dim workbook1worksheet1range1 As Microsoft.Office.Interop.Excel.Range
Dim workbook2 As Microsoft.Office.Interop.Excel.Workbook
Dim workbook2worksheet1 As Microsoft.Office.Interop.Excel.Worksheet
Dim workbook2worksheet1range1 As Microsoft.Office.Interop.Excel.Range
excelApplication = New Microsoft.Office.Interop.Excel.Application
excelApplication.Visible = False
excelApplication.DisplayAlerts = False
workbook1 = excelApplication.Workbooks.Open(in_InputFilePath)
workbook1worksheet1 = CType(workbook1.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
workbook1worksheet1.Select(Type.Missing)
workbook1worksheet1range1 = workbook1worksheet1.UsedRange.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeVisible, Type.Missing)
workbook1worksheet1range1.Copy
workbook2 = excelApplication.Workbooks.Add()
workbook2worksheet1 = CType(workbook2.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
workbook2worksheet1range1 = workbook2worksheet1.Range("A1")
workbook2worksheet1range1.PasteSpecial()
workbook2.SaveAs(in_OutputFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV)
workbook2.Save()
workbook2.Close()
workbook1.Close()
excelApplication.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook2worksheet1range1)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook2worksheet1)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook2)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1worksheet1range1)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1worksheet1)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook1)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication)