I require to create excel files (with multiple sheets) using .net on a production server (Windows Server 2008) where Microsoft Office is not installed. Can this be fulfilled with the help of VS2010 and Windows Server 2008. or is there any way to achieve this by using office webapps.
-
2Possible duplicate of stackoverflow.com/questions/151005/…– Brian ClapperCommented Dec 31, 2010 at 13:16
-
Why the down vote? New user...– jgauffinCommented Dec 31, 2010 at 13:18
-
The answers to stackoverflow.com/questions/151005 mentions many libraries that achieve what you're looking for. Your problem really is a problem of choice.– CodoCommented Dec 31, 2010 at 15:43
5 Answers
I use the OpenXML SDK which is intended for that purpose. It doesn't allow you to actually calculate and run functions in a server environment but its very good for creating and manipulating workbooks.
Download:
Documentation:
-
when referencing products/libraries/anything, please include links. Commented Jan 18, 2011 at 5:11
-
Don't you find OpenXML extremely difficult to use ? Just to do something simple like add a hyperlink takes a huge amount of code.– DavidCommented Apr 13, 2013 at 17:17
You can use this library: http://epplus.codeplex.com/releases/view/42439
It skips use of Excel binaries, in favor of the Excel XML format, therefore writing plain, simple text which Excel can understand and render properly.
If the target is office 2007 or 2010, you can try with this free library http://epplus.codeplex.com/
If the target is <= to 2003 you can try with GemBox.Spreadsheet
The free version is limited to : Maximum number of rows per sheet is 150. Maximum number of sheets per workbook is 5.
-
I tried EPPlus. I just get a NullReferenceException within the library when it tries to read the Excel files I'm working with. Commented Jan 18, 2011 at 5:09
Try this:
Sub exportExcel(ByVal grdView As DataGridView, ByVal fileName As String, _
ByVal fileExtension As String, ByVal filePath As String)
' Choose the path, name, and extension for the Excel file
Dim myFile As String = filePath & "\" & fileName & fileExtension
' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, False)
Try
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")
' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" " & _
"ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0"" ss:Pattern=""Solid""/>")
fs.WriteLine(" </ss:Style>")
' Styles for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""EasyWorks"">")
fs.WriteLine(" <ss:Table>")
For i As Integer = 0 To grdView.Columns.Count - 1
fs.WriteLine(String.Format(" <ss:Column ss:Width=""{0}""/>", _
grdView.Columns.Item(i).Width))
Next
fs.WriteLine(" <ss:Row>")
For i As Integer = 0 To grdView.Columns.Count - 1
If grdView.Columns(i).Visible Then
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""1"">" & _
"<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
grdView.Columns.Item(i).HeaderText))
End If
Next
fs.WriteLine(" </ss:Row>")
' Check for an empty row at the end due to Adding allowed on the DataGridView
Dim subtractBy As Integer, cellText As String
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else subtractBy = 1
' Write contents for each cell
For i As Integer = 0 To grdView.RowCount - subtractBy
If grdView.Rows(i).Visible Then
fs.WriteLine(String.Format(" <ss:Row ss:Height=""{0}"">", _
grdView.Rows(i).Height))
For intCol As Integer = 0 To grdView.Columns.Count - 1
If grdView.Columns(intCol).Visible Then
cellText = grdView.Item(intCol, i).Value.ToString
' Check for null cell and change it to empty to avoid error
If cellText = vbNullString Then cellText = ""
fs.WriteLine(String.Format(" <ss:Cell ss:StyleID=""2"">" & _
"<ss:Data ss:Type=""String"">{0}</ss:Data></ss:Cell>", _
cellText.ToString))
End If
Next
fs.WriteLine(" </ss:Row>")
End If
Next
' Close up the document
fs.WriteLine(" </ss:Table>")
fs.WriteLine("</ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
Catch ex As Exception
MessageBox.Show(ex.Message, "ERROR: ", MessageBoxButtons.OK, MessageBoxIcon.Error)
Err.Clear()
Finally
myFile = Nothing
fs = Nothing
End Try
End Sub