Create excel file in .net without installed office

Question

I need 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 done using VS2010 and Windows Server 2008. or is there any way to achieve this using office webapps.

Answer 1

I am using the OpenXML SDK which is designed for this purpose. This does not allow you to actually compute and run functions in a server environment, but it is very useful for creating and managing books.

Download:

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en

Documentation:

http://msdn.microsoft.com/en-us/library/bb448854.aspx

Answer 2

You can use this library: http://epplus.codeplex.com/releases/view/42439

It skips the use of Excel binaries in favor of the Excel XML format, so I write simple, simple text that Excel can correctly understand and display.

Answer 3

If the goal is the office of 2007 or 2010, you can try this free library http://epplus.codeplex.com/

If the goal is <= before 2003, you can try with GemBox.Spreadsheet

The free version is limited: The maximum number of lines per sheet is 150. The maximum number of sheets per book is 5.

Answer 4

Use the official Microsoft OpenXML SDK.

Answer 5

Try the following:

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