.Net List<T>数据集转Excel

方法一：添加对Microsoft.Office.Interop.Excel的引用

  /// <summary>
        /// 将List保存为Excel文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fileName"></param>
        /// <param name="data"></param>
        public static void ExportsListToExcel<T>( List<T> data,string fileName)
        {
            if (data != null)
            {
                // 创建Excel应用程序对象
                Application excApp = new Application();
                Workbook workBook = excApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet workSeet = workBook.Worksheets[1]; //取得sheet1
                Range range = null;
                int tableCount = data.Count;//数据总记录数量
                var type = typeof(T);
                var properties = type.GetProperties();
                //Excel列名称
                for (int i = 0; i < properties.Length; i++)
                {
                    workSeet.Cells[1, i + 1] = properties[i].Name;

                    //设置标题的样式
                    range = (Range)workSeet.Cells[1, i + 1];
                    //range.Font.Bold = true; //粗体
                    range.Font.Size = "12";//字体大小
                    range.Font.Name = "宋体";
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter; //居中
                    range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, null); //背景色
                    range.EntireColumn.AutoFit(); //自动设置列宽
                    range.EntireRow.AutoFit(); //自动设置行高
                }
                //处理数据记录
                for (int r = 0; r < data.Count; r++)
                {
                    for (int c = 0; c < properties.Length; c++)
                    {
                        //写入内容
                        workSeet.Cells[r + 2, c + 1] = "'" + properties[c].GetValue(data[r]);
                        //设置样式
                        range = (Range)workSeet.Cells[r + 2, c + 1];
                        range.Font.Size = 9; //字体大小
                        range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, null); //加边框
                        range.EntireColumn.AutoFit(); //自动调整列宽
                    }
                }
                range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin;
                if (data.Count > 1)
                {
                    range.Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
                }
                try
                {
                    workBook.Saved = true;
                    workBook.SaveCopyAs(fileName);
                }
                catch
                {
                }
                workBook.Close();
                if (excApp != null)
                {
                    excApp.Workbooks.Close();
                    excApp.Quit();
                    int generation = GC.GetGeneration(excApp);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excApp);
                    excApp = null;
                    GC.Collect(generation);
                }
                #region 强行杀死最近打开的Excel进程
                System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                System.DateTime startTime = new DateTime();
                int m, killID = 0;
                for (m = 0; m < excelProc.Length; m++)
                {
                    if (startTime < excelProc[m].StartTime)
                    {
                        startTime = excelProc[m].StartTime;
                        killID = m;
                    }
                }
                if (excelProc.Length > 0)
                {
                    if (excelProc[killID].HasExited == false)
                    {
                        excelProc[killID].Kill();
                    }
                }
                #endregion
            }
        }

方法二

/// <summary>
        /// 将List保存为Excel文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="fileName"></param>
        /// <param name="ignoreColumnList">不需要的数据列</param>
        public static void ListToCsv<T>(List<T> data, string fileName, List<string> ignoreColumnList)
        {
            string title = "";
            try
            {
                FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate);
                StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
                var type = typeof(T);
                var properties = type.GetProperties();
                //Excel列名称
                for (int i = 0; i < properties.Length; i++)
                {
                    if (ignoreColumnList.Contains(properties[i].Name))
                        continue;
                    title += properties[i].Name + "\t"; //栏位：自动跳到下一单元格
                }
                title = title.Substring(0, title.Length - 1) + "\n";
                sw.Write(title);
                for (int r = 0; r < data.Count; r++)
                {
                    string line = "";
                    for (int c = 0; c < properties.Length; c++)
                    {
                        if (ignoreColumnList.Contains(properties[c].Name))
                            continue;
                        line += properties[c].GetValue(data[r]) + "\t"; //内容：自动跳到下一单元格

                    }
                    line = line.Substring(0, line.Length - 1) + "\n";
                    sw.Write(line);
                }
                sw.Close();
                fs.Close();
            }
            catch
            {

            }
        }

方法三

使用NPOI生成Excel

 /// <summary>
        /// List转Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="columnList"></param>
        /// <param name="fileRelativePath">返回的相对路径</param>
        public static bool ExportToExcel<T>(List<T> data, List<string> columnList, out string fileRelativePath)
        {
            string rootPath = AppDomain.CurrentDomain.BaseDirectory.Replace(@"\", @"/");
            string uploadFilePath = ConfigurationManager.AppSettings["FilePath"].ToString();
            if (string.IsNullOrWhiteSpace(uploadFilePath))
            {
                uploadFilePath = "/UploadFiles";
            }
            string filePath = "";
            filePath = rootPath.Substring(0, rootPath.LastIndexOf("/")) + uploadFilePath + "/ExcelFiles/";
            CreatePath(filePath);
            try
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();//初始化文件
                ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
                //单元格样式
                ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.BorderTop = BorderStyle.Thin;
                cellStyle.BorderLeft = BorderStyle.Thin;
                cellStyle.BorderRight = BorderStyle.Thin;
                cellStyle.BorderBottom = BorderStyle.Thin;
                //create a font style
                IFont font = hssfworkbook.CreateFont();
                font.FontHeight = 10 * 20;
                cellStyle.SetFont(font);
                var type = typeof(T);
                var properties = type.GetProperties();
                IRow firstrow = sheet1.CreateRow(0);//第一行，列名称
                int colIndex = 0;//列
                //第一列默认为序号列
                ICell cellNo = firstrow.CreateCell(colIndex);
                cellNo.SetCellValue("序号");
                cellNo.CellStyle = cellStyle;
                colIndex++;
                //Excel列名称
                for (int i = 0; i < properties.Length; i++)
                {
                    if (!columnList.Contains(properties[i].Name))
                        continue;
                    var displayName = properties[i].GetCustomAttribute<DisplayNameAttribute>();
                    ICell cell = firstrow.CreateCell(colIndex);
                    if (displayName != null)
                    {
                        //firstrow.CreateCell(colIndex).SetCellValue(displayName.DisplayName);
                        cell.SetCellValue(displayName.DisplayName);
                    }
                    else
                    {
                        cell.SetCellValue(properties[i].Name);
                    }
                    cell.CellStyle = cellStyle;
                    colIndex++;
                }
                //设置列宽
                for (int n = 0; n < colIndex; n++)
                {
                    sheet1.SetColumnWidth(n, 4000);
                }
                for (int i = 0; i < data.Count; i++)
                {
                    colIndex = 0;
                    IRow row = sheet1.CreateRow(i + 1);
                    //第一列序号列
                    ICell cellSort = row.CreateCell(colIndex);
                    cellSort.SetCellValue(i + 1);
                    cellSort.CellStyle = cellStyle;
                    colIndex++;
                    for (int j = 0; j < properties.Length; j++)
                    {
                        if (!columnList.Contains(properties[j].Name))
                            continue;
                        Type propertyType = properties[j].PropertyType;//获取数据类型
                        string cellValue = "";
                        if (propertyType == typeof(DateTime) || propertyType == typeof(DateTime?))
                        {
                            if (properties[j].GetValue(data[i]) != null)
                            {
                                cellValue = Convert.ToDateTime(properties[j].GetValue(data[i])).ToString("yyyy-MM-dd");
                            }
                        }
                        else
                        {
                            if (properties[j].GetValue(data[i]) != null)
                            {
                                cellValue = properties[j].GetValue(data[i]).ToString();
                            }
                        }
                        //row.CreateCell(colIndex).SetCellValue(cellValue);
                        ICell cell = row.CreateCell(colIndex);
                        cell.SetCellValue(cellValue);
                        cell.CellStyle = cellStyle;
                        colIndex++;
                    }
                }
                string fileName = Guid.NewGuid().ToString() + ".xls";
                filePath = filePath + fileName;
                FileStream file = new FileStream(filePath, FileMode.Create);
                hssfworkbook.Write(file);
                file.Close();
                fileRelativePath = "/" + filePath.Replace(rootPath, "");
                return true;
            }
            catch (Exception ex)
            {
                fileRelativePath = "";
                return false;
            }
        }