create excel file in .net without office installed

Question

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.

Answer 1

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:

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

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

Answer 3

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.

Answer 4

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.

Answer 5

Use the official Microsoft OpenXML SDK.