导出 Excel

最近项目需要一个导出功能， 我一开始使用传统的直接写html到文件的方式， 但是发现着这种方法导出的Ecxel 文件在office 2010 中打开不正常， 然后只好采用这个种COM 的方式了。现将代码贴出来供大家参考。

期间你很可能遇到的异常（我都遇到过，使我放弃的心都有了）， 解决方案也贴出来，让大家少苦逼一会：

1）Error accessing COM components

      参考我的另一篇博客： http://blog.csdn.net/q573625392/article/details/8931597

2）Microsoft Office Excel cannot access the file 'XXXX'

      参考我的另一篇博客： http://blog.csdn.net/q573625392/article/details/8931461

 

        
using Microsoft.Office.Interop.Excel;

....

protected FileContentResult ExportToExcel(string fileName, Dictionary<string, System.Data.DataTable> datasources)
        {
            /*
            string tempFolders = Server.MapPath("\\Temp\\");
            string templateName = "Template.xlsx";

            if (!Directory.Exists(tempFolders))
            {
                Directory.CreateDirectory(tempFolders);
            }

            // Template file path.
            string templateFileName = Path.Combine(Server.MapPath("\\Template\\"), templateName);
            // Temp template file path.
            string tempTemplateFile = Path.Combine(tempFolders, this.UserAlias + "-" + templateName);

            // Copy file.
            System.IO.File.Copy(templateFileName, tempTemplateFile, true);
            */

            // Create a new application 
            Application excelApp = new Application();
            excelApp.Visible = false;

            // open a workbook 
            /*
            Workbook newWorkbook = excelApp.Workbooks.Open(templateFileName, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            */
            Workbook newWorkbook = excelApp.Workbooks.Add();

            int counter = 1;
            foreach (var key in datasources.Keys)
            {
                System.Data.DataTable dataSource = datasources[key];

                if (counter > 3)
                {
                    newWorkbook.Worksheets.Add();
                }

                Worksheet excelSheet = (Worksheet)newWorkbook.Worksheets["Sheet" + counter.ToString()];
                excelSheet.Name = key;

                Range tempRange = null;
                for (int i = 0; i < dataSource.Columns.Count; i++)
                {
                    tempRange = excelSheet.get_Range(GetColumnNameByIndex(i) + "1", Type.Missing);
                    tempRange.Value2 = dataSource.Columns[i].ColumnName;
                }

                string text = string.Empty; ;
                for (int i = 0; i < dataSource.Rows.Count; i++)
                {
                    DataRow dr = dataSource.Rows[i];
                    for (int j = 0; j < dataSource.Columns.Count; j++)
                    {
                        text = dr[j].ToString();

                        tempRange = excelSheet.get_Range(GetColumnNameByIndex(j) + (i + 2).ToString());
                        tempRange.Value2 = text;
                     
                        /*if (text.Length > 10 && text.Length < 50)
                        {
                            tempRange.Columns.ColumnWidth = text.Length * 1.5;
                            //tempRange.Columns.AutoFit();
                        }*/
                    }
                }

                counter++;
            }

            //range3.Borders.Color = Color.FromArgb(123, 231, 32).ToArgb();
            //range3.Font.Color = Color.Red.ToArgb();
            //range3.Font.Name = "Arial";
            //range3.Font.Size = 9;
            //range3.Orientation = 90;   //vertical
            //range3.Columns.HorizontalAlignment = Constants.xlCenter;
            //range3.VerticalAlignment = Constants.xlCenter;
            //range3.Interior.Color = Color.FromArgb(192, 192, 192).ToArgb();

            //range3.Columns.AutoFit();//adjust the column width automatically

            string tempFolderPath = Path.GetDirectoryName(fileName);
            if (!Directory.Exists(tempFolderPath))
            {
                Directory.CreateDirectory(tempFolderPath);
            }
            if (System.IO.File.Exists(fileName))
            {
                System.IO.File.Delete(fileName);
            }

            newWorkbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            newWorkbook.Close(false, Type.Missing, Type.Missing);
            excelApp.Quit();

            FileContentResult fcr = new FileContentResult(System.IO.File.ReadAllBytes(fileName), "application/ms-excel");
            fcr.FileDownloadName = Path.GetFileName(fileName);

            System.IO.File.Delete(fileName);

            GC.Collect();
            return fcr;
        }

        private static string[] alphabet = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
        private static string GetColumnNameByIndex(int index)
        {
            if (index < alphabet.Length)
            {
                return alphabet[index];
            }
            return alphabet[index / alphabet.Length - 1] + alphabet[index % alphabet.Length];
        }
    }