question

Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 asked Castorix31 commented

Way to create file

Hi,
How to create .doc, .docx, xls, xlsx files, using VS 2017?

dotnet-csharpdotnet-visual-basic
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Castorix31 avatar image
0 Votes"
Castorix31 answered Castorix31 commented

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)



· 12
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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;
              ...



0 Votes 0 ·

Have you installed the Package DocumentFormat.OpenXml ? (then the Imports/using)

(and you post C# code with a VB Tag...)

0 Votes 0 ·

How to install OpenXml package?
How to put C# code tag?

0 Votes 0 ·
Show more comments
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 answered cleric145-6909 commented

Thanks to all.
Karen,
How to ensure SpreadSheetLight is available within VS?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Don't use external libraries

Use Open XML which works fine or Office Interop if Office is installed

0 Votes 0 ·

How to ensure OpenXML is available to use, in VS?

0 Votes 0 ·

It is an official package from Microsoft
Always use Microsoft products when they exist

0 Votes 0 ·