none
How to download Excel file with Multiple Sheets in asp.net RRS feed

  • Question

  • I'm working on exporting values in my xml to the excel.Where I got 17 languages and adding to 17 sheets in the excel file.Now I want to download it the local folder from the server.Can anyone help me out and my code is

    string version; int colCount = 0, rowCount = 0;
                IEnumerable<string> worksheetNames = default(IEnumerable<string>); ;
                XElement sitecoreItemFields = default(XElement);
                SitecoreCompare sitecoreCompare = new SitecoreCompare();
                SitecoreManager sitecoreManager = new SitecoreManager();
                UnmatchedFieldList = new List<UnmatchedFieldInfo>();
                if (File.Exists(excelPath))
                {
                    var vFile = new LinqToExcel.ExcelQueryFactory(excelPath);
                    worksheetNames = vFile.GetWorksheetNames();
                }
                foreach (LanguageInfo language in allLanguages)
                {
                    UnmatchedFieldList = new List<UnmatchedFieldInfo>();
                    if (sitecoreManager.GetSitecoreInstance())
                    {
                        version = sitecoreCompare.GetLatestVersionFromDevelopment(ProductId, language.Culture);
                        sitecoreItemFields = sitecoreManager.GetItemFieldsInDevelopment(ProductId, language.Culture, version, true);
                        XElement itemXml = sitecoreManager.GetItemXmlInDevelopment(ProductId);
                    }
                    else
                    {
                        version = sitecoreCompare.GetLatestVersionFromDevelopment(ProductId, language.Culture);
                        sitecoreItemFields = sitecoreManager.GetItemFieldsInDevelopment(ProductId, language.Culture, version, true);
                    }
                    excelApplication = new Microsoft.Office.Interop.Excel.Application();
                   // excelApplication.Visible = true;
                    excelApplication.DisplayAlerts = false;
                    foreach (string fieldids in SelectedFieldIDs)
                    {
                        UnmatchedFieldInfo fieldInfo = new UnmatchedFieldInfo();
                        var selectedFieldItem = sitecoreItemFields.Elements("field").Where(i => i.Attribute("fieldid").Value == fieldids).FirstOrDefault();
                        if (selectedFieldItem != null)
                        {
                            fieldInfo.Id = fieldids;
                            fieldInfo.DevValue = selectedFieldItem.Element("value").Value;
                            fieldInfo.Name = selectedFieldItem.Attribute("name").Value;
                            UnmatchedFieldList.Add(fieldInfo);
                        }
                        else
                        {
                            fieldInfo.Id = fieldids;
                            fieldInfo.DevValue = string.Empty;
                            fieldInfo.Name = string.Empty;
                            UnmatchedFieldList.Add(fieldInfo);
                            SaveAndCollecttheGarbage(excelApplication, excelPath, mSheets);
                        }
                    }
                    if (!File.Exists(excelPath))
                    {
                        mWorkBook = excelApplication.Workbooks.Add(Missing.Value);
                        mSheets = mWorkBook.Worksheets;
                        mWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)mSheets.get_Item("Sheet1");
                        mWorkSheet.Name = language.Culture;
                        int i = 1, j = 0;
                        foreach (UnmatchedFieldInfo info in UnmatchedFieldList)
                        {
                            mWorkSheet.Cells[1, j + i] = info.Name;
                            mWorkSheet.Cells[2, j + i] = info.DevValue;
                            j++;
                        }
                        Microsoft.Office.Interop.Excel.Range range = mWorkSheet.UsedRange;
                        colCount = range.Columns.Count;
                        mWorkSheet.Cells[1, colCount + 1] = "ITEMNAME";
                        mWorkSheet.Cells[2, colCount + 1] = itemName;
                        mWorkSheet.Cells[1, colCount + 2] = "ITEMID";
                        mWorkSheet.Cells[2, colCount + 2] = ProductId;
                        mWorkSheet.Cells[1, colCount + 3] = "TEMPLATEID";
                        mWorkSheet.Cells[2, colCount + 3] = templateId;
                        mWorkSheet.Cells[1, colCount + 4] = "PARENTID";
                        mWorkSheet.Cells[2, colCount + 4] = parentId;
                    }
                    else
                    {
                        mWorkBook = excelApplication.Workbooks.Open(excelPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                        mSheets = mWorkBook.Worksheets;
                        if (worksheetNames == null || !worksheetNames.Contains(language.Culture))
                        {
                            mWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)mSheets.Add();
                            mWorkSheet.Name = language.Culture;
                        }
                        else
                        {
                            mWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)mSheets.get_Item(language.Culture);
                            Microsoft.Office.Interop.Excel.Range range = mWorkSheet.UsedRange;
                            colCount = range.Columns.Count;
                            rowCount = range.Rows.Count;
                        }
                        int i = 0;
                        foreach (UnmatchedFieldInfo infoList in UnmatchedFieldList)
                        {
                            if (worksheetNames == null || !worksheetNames.Contains(language.Culture))
                            {
                                i++;
                                mWorkSheet.Cells[rowCount + 1, i] = infoList.Name;
                                mWorkSheet.Cells[rowCount + 2, i] = infoList.DevValue;
                            }
                            else
                            {
                                i++;
                                mWorkSheet.Cells[rowCount + 1, i] = infoList.DevValue;
                            }
                        }
                        if (worksheetNames == null || !worksheetNames.Contains(language.Culture))
                        {
                            mWorkSheet.Cells[rowCount + 1, UnmatchedFieldList.Count + 1] = "ITEMNAME";
                            mWorkSheet.Cells[rowCount + 2, UnmatchedFieldList.Count + 1] = itemName;
                            mWorkSheet.Cells[rowCount + 1, UnmatchedFieldList.Count + 2] = "ITEMID";
                            mWorkSheet.Cells[rowCount + 2, UnmatchedFieldList.Count + 2] = ProductId;
                            mWorkSheet.Cells[rowCount + 1, UnmatchedFieldList.Count + 3] = "TEMPLATEID";
                            mWorkSheet.Cells[rowCount + 2, UnmatchedFieldList.Count + 3] = templateId;
                            mWorkSheet.Cells[rowCount + 1, UnmatchedFieldList.Count + 4] = "PARENTID";
                            mWorkSheet.Cells[rowCount + 2, UnmatchedFieldList.Count + 4] = parentId;
                            count = UnmatchedFieldList.Count;
                        }
                        else
                        {
                            mWorkSheet.Cells[rowCount + 1, count + 1] = itemName;
                            mWorkSheet.Cells[rowCount + 1, count + 2] = ProductId;
                            mWorkSheet.Cells[rowCount + 1, count + 3] = templateId;
                            mWorkSheet.Cells[rowCount + 1, count + 4] = parentId;
                        }
                    }
                    SaveAndCollecttheGarbage(excelApplication, excelPath, mSheets);
                }

    Thursday, April 23, 2015 4:56 AM

Answers