Click here to Skip to main content
15,902,939 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
My 64 bit server which is running 32 bit office, 
what my application does is get the data and create a excel file in server.

I am getting this error : 
System.Runtime.InteropServices.COMException (0x80040154): 
Retrieving the COM class factory for component with CLSID {xxx-xx-xxxx} 
failed due to the following error: 80040154.
I checked in GAC in my server, its missing MIcrosoft.Office.Interop.Excel.

How do i fix this issue?


What I have tried:

I check online but did not find solution how to resolve.
Posted
Updated 7-Feb-17 6:33am
Comments
[no name] 7-Feb-17 11:12am    
"I check online but did not find solution how to resolve.", if you actually searched for a solution you would have found that this is not recommended or supported and you will have to find another way to do whatever it is that you are doing without using Office interop.
F-ES Sitecore 7-Feb-17 11:47am    
That's true if he is automating Excel from a website which he hasn't explicitly said he is.
[no name] 7-Feb-17 20:07pm    
"create a excel file in server"
F-ES Sitecore 8-Feb-17 8:02am    
Needing to create a file on a server doesn't necessarily mean it's a web site, there are other types of server than a web server.

EDIT: Whoops. This doesn't work without Excel installed!

The dll is in the GAC if you have Excel installed.

You can get the dll to be included in a local build. Get it here:NuGet Gallery | Microsoft.Office.Interop.Excel 15.0.4795.1000[^]

Nuget will sort out adding it to the build so it should be in the bin folder after you compile
 
Share this answer
 
v2
Comments
[no name] 7-Feb-17 12:18pm    
I did that already. Application runs fine. But in my test server,this component is missing. How do I copy it from my local gac to server? Or install it in server. My app is a scheduled task.
Andy Lanng 7-Feb-17 12:19pm    
Hmm - shudda been built in.
Grap the dll, wherever it was downloaded to and paste it in to the bin folder of your service
Andy Lanng 7-Feb-17 12:21pm    
Oh Wait!!
dang, I don't use that on the production server. It still needs licence!
Hang on - I have an OpenXml solution. will post
s23user 9-Feb-17 22:09pm    
So do I need to have excel installed in my server to create an excel spread sheet. I guess that's right, since the code works on my local where excel is installed
Andy Lanng 10-Feb-17 3:44am    
To use interope, yes. To use solution #2, no. Interope is faster than openXml for large datasets
Ok - attempt #2

Use OpenXml: NuGet Gallery | DocumentFormat.OpenXml 2.5.0[^]

All xslx docs are just a few XML docs zipped up (seriously, rename any .xlsx doc to .zip and open it to see all the xml)
The docs are optimized to save space so are pretty confusing to read and write. I came up with some methods to help.

This is pretty slow for very large datasets so it could be optimized:

Extension Method:
C#
public static Class Extensions{
  public static void SaveAsExcel<T>(this IEnumerable<T> source, string filePath, string worksheetName)
  {
    //Spread
    ExportToExcelUtility.CreateExcelDocument(ExportToExcelUtility.ToDataTable(source), filePath, worksheetName,
                new Dictionary<string, string>(), new Dictionary<string, int>());

  }
}


the rest of the code I think I got from here:Export to Excel using C# | Sharmili unplugged.......[^]

C#
public sealed class ExportToExcelUtility
    {
        /// <summary>
        /// Convert the list to Data table.
        /// </summary>
        /// <param name="items">List</param>
        /// <returns>DataTable</returns>
        public static DataTable ToDataTable<T>(IEnumerable<T> items)
        {

            DataTable dataTable = new DataTable(typeof(T).Name);
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                dataTable.Columns.Add(prop.Name);//Setting column names
            }
            foreach (T item in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    values[i] = Props[i].GetValue(item, null);//Insert values.
                }
                dataTable.Rows.Add(values);
            }
            return dataTable;
        }

        /// <summary>
        /// Call this method from the Page providing the desired information
        /// </summary>
        /// <param name="dataTable">The records to be written in excel</param>
        /// <param name="excelFilename">Name of the file</param>
        /// <param name="sheetName">Name of the sheet</param>
        /// <param name="filters">Search key and value based on which the datatable is generated</param>
        /// <param name="columnSize">column name and size</param>
        /// <returns></returns>
        public static bool CreateExcelDocument(DataTable dataTable, string excelFilename, string sheetName, Dictionary<string, string> filters, Dictionary<string, int> columnSize)
        {
            try
            {
                bool exists = File.Exists(excelFilename);
                SpreadsheetDocument objExcelDoc;
                if (exists)
                {
                    try
                    {
                        objExcelDoc = SpreadsheetDocument.Open(excelFilename, true);
                    }
                    catch
                    {
                        File.Delete(excelFilename);
                        objExcelDoc = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook);
                    }
                }
                else
                    objExcelDoc = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook);

                int cellSize;
                WorkbookPart wbp = objExcelDoc.AddWorkbookPart();
                WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                Workbook wb = new Workbook();
                FileVersion fv = new FileVersion();
                fv.ApplicationName = "Microsoft Office Excel";
                Worksheet workSheet = new Worksheet();
                WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
                wbsp.Stylesheet = CreateStylesheet();
                wbsp.Stylesheet.Save();
                Columns columns = new Columns();
                for (int i = 0; i < columnSize.Count(); i++)
                {
                    columnSize.TryGetValue(columnSize.Keys.ElementAt(i).ToString(), out cellSize);
                    columns.Append(CreateColumnData(Convert.ToUInt32(i - 1), Convert.ToUInt32(i - 1), cellSize));
                }
                workSheet.Append(columns);
                SheetData sheetData = new SheetData();
                for (UInt32 i = 2; i <= 1 + filters.Count(); i++)
                {
                    sheetData.Append(CreateFilters(i, filters));
                }
                sheetData.Append(CreateColumnHeader(Convert.ToUInt32(filters.Count() + 3), columnSize));
                UInt32 index = Convert.ToUInt32(filters.Count() + 4);
                foreach (DataRow dr in dataTable.Rows)
                {
                    sheetData.Append(CreateContent(index, dr, columnSize.Count()));
                    index++;
                }
                workSheet.Append(sheetData);
                wsp.Worksheet = workSheet;
                Sheets sheets = new Sheets();
                Sheet sheet = new Sheet();
                sheet.Name = sheetName;
                sheet.SheetId = 1;
                sheet.Id = wbp.GetIdOfPart(wsp);
                sheets.Append(sheet);
                wb.Append(fv);
                wb.Append(sheets);
                objExcelDoc.WorkbookPart.Workbook = wb;
                objExcelDoc.WorkbookPart.Workbook.Save();
                objExcelDoc.Close();
            }
            catch (Exception ex)
            {
                throw;
            }
            return true;
        }

        /// <summary>
        /// Create column for storing data by passing the start column index, end column index and column width
        /// </summary>
        /// <param name="StartColumnIndex">start column index</param>
        /// <param name="EndColumnIndex">end column index</param>
        /// <param name="ColumnWidth">width of each column</param>
        /// <returns>column</returns>
        private static Column CreateColumnData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
        {
            Column column;
            column = new Column();
            column.Min = StartColumnIndex;
            column.Max = EndColumnIndex;
            column.Width = ColumnWidth;
            column.CustomWidth = true;
            return column;
        }

        /// <summary>
        /// Writes the row to the excel by reading each datarow from the datatable
        /// </summary>
        /// <param name="index">row index</param>
        /// <param name="dr">data row</param>
        /// <param name="columns">number of columns</param>
        /// <returns></returns>
        private static Row CreateContent(UInt32 index, DataRow dr, int columns)
        {
            Row objRow = new Row();
            Cell objCell;
            try
            {
                objRow.RowIndex = index;
                for (int i = 0; i < columns; i++)
                {
                    objCell = new Cell();
                    objCell.StyleIndex = 5;
                    objCell.DataType = CellValues.String;
                    objCell.CellReference = (char)(i + 65) + index.ToString();
                    objCell.CellValue = new CellValue(dr.ItemArray[i].ToString());
                    objRow.Append(objCell);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            return objRow;
        }

        /// <summary>
        /// Defines the Style sheet for the excel.
        /// </summary>
        /// <returns>Stylesheet</returns>
        private static Stylesheet CreateStylesheet()
        {
            Stylesheet ss = new Stylesheet();
            Fonts fts = new Fonts();
            DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();
            ftn.Val = StringValue.FromString("Calibri");
            DocumentFormat.OpenXml.Spreadsheet.FontSize ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
            ftsz.Val = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ftn = new FontName();
            ftn.Val = StringValue.FromString("Palatino Linotype");
            ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
            ftsz.Val = DoubleValue.FromDouble(18);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);
            fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
            Fills fills = new Fills();
            Fill fill;
            PatternFill patternFill;
            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.None;
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.Gray125;
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fill = new Fill();
            patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.Solid;
            patternFill.ForegroundColor = new ForegroundColor();
            patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("CDCDCD");
            patternFill.BackgroundColor = new BackgroundColor();
            patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
            fill.PatternFill = patternFill;
            fills.Append(fill);
            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            Borders borders = new Borders();
            Border border = new Border();
            border.LeftBorder = new LeftBorder();
            border.RightBorder = new RightBorder();
            border.TopBorder = new TopBorder();
            border.BottomBorder = new BottomBorder();
            border.DiagonalBorder = new DiagonalBorder();
            borders.Append(border);
            border = new Border();
            border.LeftBorder = new LeftBorder();
            border.LeftBorder.Style = BorderStyleValues.Thin;
            border.RightBorder = new RightBorder();
            border.RightBorder.Style = BorderStyleValues.Thin;
            border.TopBorder = new TopBorder();
            border.TopBorder.Style = BorderStyleValues.Thin;
            border.BottomBorder = new BottomBorder();
            border.BottomBorder.Style = BorderStyleValues.Thin;
            border.DiagonalBorder = new DiagonalBorder();
            borders.Append(border);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
            CellStyleFormats csfs = new CellStyleFormats();
            CellFormat cf = new CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            csfs.Append(cf);
            csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
            uint iExcelIndex = 164;
            NumberingFormats nfs = new NumberingFormats();
            CellFormats cfs = new CellFormats();
            NumberingFormat nfForcedText = new NumberingFormat();
            nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfForcedText.FormatCode = StringValue.FromString("@");
            nfs.Append(nfForcedText);
            cf = new CellFormat();
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            cf = new CellFormat();
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            cf = new CellFormat();
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 1;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            cf = new CellFormat();
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cfs.Append(cf);
            cf = new CellFormat();
            cf.FontId = 0;
            cf.FillId = 2;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            cf = new CellFormat();
            cf.NumberFormatId = nfForcedText.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 2;
            cf.BorderId = 1;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);
            ss.Append(nfs);
            ss.Append(fts);
            ss.Append(fills);
            ss.Append(borders);
            ss.Append(csfs);
            ss.Append(cfs);
            CellStyles css = new CellStyles();
            CellStyle cs = new CellStyle();
            cs.Name = StringValue.FromString("Normal");
            cs.FormatId = 0;
            cs.BuiltinId = 0;
            css.Append(cs);
            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
            ss.Append(css);
            DifferentialFormats dfs = new DifferentialFormats();
            dfs.Count = 0;
            ss.Append(dfs);
            TableStyles tss = new TableStyles();
            tss.Count = 0;
            tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
            tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
            ss.Append(tss);
            return ss;
        }
        /// <summary>
        /// Create the header row provided with the row index and header list with each column size
        /// </summary>
        /// <param name="index">row index</param>
        /// <param name="headerList">header name and column width</param>
        /// <returns></returns>
        private static Row CreateColumnHeader(UInt32 index, Dictionary<string, int> headerList)
        {
            Row objRow = new Row();
            objRow.RowIndex = index;
            Cell objCell;
            for (int i = 0; i < headerList.Count(); i++)
            {
                objCell = new Cell();
                objCell.DataType = CellValues.String;
                objCell.StyleIndex = 6;
                objCell.CellReference = Convert.ToChar(65 + i) + index.ToString();
                objCell.CellValue = new CellValue(headerList.Keys.ElementAt(i).ToString());
                objRow.Append(objCell);
            }
            return objRow;
        }
        /// <summary>
        /// Creating the filters based on which data row is generated. For creating header templates
        /// </summary>
        /// <param name="index">Row index</param>
        /// <param name="filters">Search key and its respective value</param>
        /// <returns></returns>
        private static Row CreateFilters(UInt32 index, Dictionary<string, string> filters)
        {
            Row objRow = new Row();
            try
            {
                objRow.RowIndex = index;
                Cell objcell;
                objcell = new Cell();
                objcell.DataType = CellValues.String;
                objcell.StyleIndex = 6;
                objcell.CellReference = "A" + index.ToString();
                objcell.CellValue = new CellValue(filters.Keys.ElementAt(Convert.ToInt32(-2 + index)).ToString());
                objRow.Append(objcell);
                objcell = new Cell();
                objcell.DataType = CellValues.String;
                objcell.StyleIndex = 5;
                objcell.CellReference = "B" + index.ToString();
                objcell.CellValue = new CellValue(Convert.ToString(filters.ElementAt(Convert.ToInt32(-2 + index)).Value));
                objRow.Append(objcell);
            }
            catch (Exception ex)
            {
                throw;
            }
            return objRow;
        }
    }

    //2) Add the following namespaces to the page where you need to implement export to excel functionality
    //using System.Web.UI;
    //using System.Web.UI.WebControls;
    //using DocumentFormat.OpenXml.Spreadsheet;
    //using DocumentFormat.OpenXml;
    //using System.Data;
    //using DocumentFormat.OpenXml.Packaging;
    //using System.Reflection;
    //using System.IO;
    //3) In the event that requires to perform export to excel, call the CreateExcelDocument by providing the datatable generated, filename, sheetname, search filters as key value pair, column names and each column size as key value pairs.
    //protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
    //{
    //try
    //{
    //Dictionary<string, string> filters = new Dictionary<string, string>();
    //filters.Add("Search Key","Search Value"); to add the filters for generating the template rows
    //Dictionary<string, int> Columns = new Dictionary<string, int>();
    //Columns.Add("Column name",size in integer); to add the column name and its respective size
    //DataTable DataList = new DataTable();
    //DataList = ExportToExcelUtility.ToDataTable(call the method that returns The list to be written in Excel);
    //string date = DateTime.Now.ToString("ddMMMyyHHmmtt");
    //string filename = System.IO.Path.GetTempPath() + (Session["FileName"].ToString()) + DateTime.Now.ToString("ddMMMyyHHmmtt") + ".xlsx";
    //if (ExportToExcelUtility.CreateExcelDocument(DataList, filename, date, filters, Columns))
    //{
    //Response.ClearContent();
    //FileStream objFileStream = new FileStream(filename, FileMode.Open, FileAccess.Read);
    //byte[] data1 = new byte[objFileStream.Length];
    //objFileStream.Read(data1, 0, data1.Length);
    //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    //Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", filename));
    //Response.BinaryWrite(data1);
    //}
    //}
    //catch (Exception ex)
    //{
    //}
    //Response.End();
    //}
 
Share this answer
 
Comments
s23user 9-Feb-17 22:08pm    
Thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900