Hi,
How to create .doc, .docx, xls, xlsx files, using VS 2017?
Hi,
How to create .doc, .docx, xls, xlsx files, using VS 2017?
You can use Open XML SDK or Word/Excel Interop
For example, with Word Interop :
' Add reference to Microsoft.Office.Interop.Word
then
Dim oWordApplication As Word.Application = New Word.Application()
Dim oMissing As Object = System.Reflection.Missing.Value
Dim oWordDocument As Word.Document = oWordApplication.Documents.Add(oMissing, oMissing, oMissing, oMissing)
oWordDocument.Content.SetRange(0, 0)
oWordDocument.Content.Text = "This is a test"
Dim sPath As String = Path.GetTempPath() ' %localappdata%\Temp
Dim oFilename As Object = sPath + "test.docx"
oWordDocument.SaveAs2(oFilename)
oWordDocument.Close()
oWordApplication.Quit()
Marshal.ReleaseComObject(oWordApplication)
Thanks a lot.
How to resolve
The type or namespace name 'WordprocessingDocument' could not be found
The type or namespace name 'Body' could not be found
due to the lines below
using (WordprocessingDocument wordprocessingDocument =WordprocessingDocument.Open(filepath, true)){// Assign a reference to the existing document body.Body body = wordprocessingDocument.MainDocumentPart.Document.Body;...
Have you installed the Package DocumentFormat.OpenXml ? (then the Imports/using)
(and you post C# code with a VB Tag...)
If you were only working with .xlsx and not .xls then SpreadSheetLight (free from NuGet) is very easy to use e.g.
Here I'm creating a new Excel file, populate and save.
Public Class PeopleData
Public Shared Function List() As List(Of Person)
Return New List(Of Person) From {
New Person() With {.FirstName = "John", .LastName = "Doe"},
New Person() With {.FirstName = "Mary", .LastName = "Adams"},
New Person() With {.FirstName = "Bob", .LastName = "Wills"}}
End Function
End Class
Class code
Public Shared Function CreateAndPopulate() As Boolean
Dim success = True
Try
Using doc As New SLDocument()
Dim style As New SLStyle
style.Font.Bold = True
doc.SetRowStyle(1, 1, style)
doc.SetCellValue("A1", "Last name")
doc.SetCellValue("B1", "First name")
Dim people = PeopleData.List()
Dim rowIndex = 0
For Each person In people
doc.SetCellValue($"A{rowIndex + 2}", people(rowIndex).LastName)
doc.SetCellValue($"B{rowIndex + 2}", people(rowIndex).FirstName)
rowIndex += 1
Next
Dim stats = doc.GetWorksheetStatistics()
doc.AutoFitColumn(1, stats.EndColumnIndex)
doc.RenameWorksheet(SLDocument.DefaultFirstSheetName, "People")
doc.SaveAs("PeopleData.xlsx")
End Using
Catch ex As Exception
Console.WriteLine($"{ex.Message}")
success = False
End Try
Return success
End Function
Export a DataTable to a new Excel file
Public Shared Sub Export(
pFileName As String,
pSheetName As String,
pDataSet As DataSet,
pTableName As String,
pColumnHeaders As Boolean)
Using doc As New SLDocument()
doc.SelectWorksheet(pSheetName)
doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataSet.Tables(pTableName), pColumnHeaders)
doc.RenameWorksheet(SLDocument.DefaultFirstSheetName, pSheetName)
doc.SaveAs(pFileName)
End Using
Read a Sheet
Imports System.IO
Imports SpreadsheetLight
Public Class ExcelOperations3
Private Shared ReadOnly Property _excelFileName() As String
Get
Return Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx")
End Get
End Property
Private Shared _sheetName As String = "Customers"
Public Shared ReadOnly Property FileExist() As Boolean
Get
Return File.Exists(_excelFileName)
End Get
End Property
Public Shared Property Exception() As Exception
Public Shared Function Read() As DataTable
Exception = Nothing
Dim dt = New DataTable()
dt.Columns.Add("CompanyName", GetType(String))
dt.Columns.Add("ContactName", GetType(String))
dt.Columns.Add("ContactTitle", GetType(String))
dt.Columns.Add("Country", GetType(String))
Try
Using doc = New SLDocument(_excelFileName, _sheetName)
Dim stats = doc.GetWorksheetStatistics()
Dim index As Integer = 2
Do While index < stats.EndRowIndex + 1
Dim col1Value = doc.GetCellValueAsString(index, 1)
Dim col2Value = doc.GetCellValueAsString(index, 2)
Dim col3Value = doc.GetCellValueAsString(index, 3)
Dim col4Value = doc.GetCellValueAsString(index, 6)
dt.Rows.Add(New Object() {col1Value, col2Value, col3Value, col4Value})
index += 1
Loop
End Using
Catch exceptionObject As Exception
Exception = exceptionObject
End Try
Return dt
End Function
End Class
More in the following repository
Depending on how deep down the rabbit hole you want to go with Word Open XML is best yet you need to spend time learning the underlying structure. My choice for word is GemBox Document and even with a 3rd party library you need to understand the structure of Excel.
[4]: https://github.com/karenpayneoregon/OpenXmlWordPressing
Thanks to all.
Karen,
How to ensure SpreadSheetLight is available within VS?
Don't use external libraries
Use Open XML which works fine or Office Interop if Office is installed
It is an official package from Microsoft
Always use Microsoft products when they exist
5 people are following this question.