【C#工具类】生成Excel

1.通过POI生成Excel：
需要引入dll，NPOI。

 public static void CreateExcelByPOIForDownload(DataTable sourceTable, string filePath, string filename)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                MemoryStream ms = new MemoryStream();
                ISheet sheet = workbook.CreateSheet();
                if (sheet != null)
                {
                    IRow headerRow = sheet.CreateRow(0);

                    // handling header.
                    foreach (DataColumn column in sourceTable.Columns)
                    {
                        if (headerRow != null)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        }
                    }

                    // handling value.
                    int rowIndex = 1;

                    foreach (DataRow row in sourceTable.Rows)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);

                        foreach (DataColumn column in sourceTable.Columns)
                        {
                            if (dataRow != null) dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                        }

                        rowIndex++;
                    }

                }
                workbook.Write(ms);
                ms.Flush();
                HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", HttpUtility.UrlEncode(filename)));
                HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                workbook = null;
                ms.Close();

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void CreateExcelByPOIForLocal(DataTable sourceTable, string filePath, string filename)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                FileStream ms = new FileStream(filePath + filename, FileMode.Append);
                ISheet sheet = workbook.CreateSheet();
                if (sheet != null)
                {
                    IRow headerRow = sheet.CreateRow(0);

                    // handling header.
                    foreach (DataColumn column in sourceTable.Columns)
                    {
                        if (headerRow != null)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        }
                    }

                    // handling value.
                    int rowIndex = 1;

                    foreach (DataRow row in sourceTable.Rows)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);

                        foreach (DataColumn column in sourceTable.Columns)
                        {
                            if (dataRow != null) dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                        }

                        rowIndex++;
                    }

                }
                workbook.Write(ms);
                ms.Close();
                ms.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

2.通过DataTable和filePath生成Excel：

public static void CreateExcelByDataTableAndFilePath(DataTable dt, string FilePath)
        {
            try
            {
                //HttpContext.Current.Response.Clear();
                //HttpContext.Current.Response.AddHeader(
                //    "content-disposition", string.Format("attachment; filename={0}", FileName));        
                //HttpContext.Current.Response.ContentType = "application/vnd.xls";
                //HttpContext.Current.Response.ContentEncoding = System.Text.UTF8Encoding.Default;  

                FileStream fileStream = new FileStream(FilePath, FileMode.OpenOrCreate
                    );
                StreamWriter sw = new StreamWriter(fileStream, System.Text.UTF8Encoding.Default);

                string colHeaders = "", ls_item = "";
                DataRow[] myRow = dt.Select();
                int i = 0;
                int cl = dt.Columns.Count;
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + "\n";
                    }
                    else
                    {
                        colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                    }
                }
                //HttpContext.Current.Response.Write(colHeaders);
                sw.Write(colHeaders);
                foreach (DataRow row in myRow)
                {
                    for (i = 0; i < cl; i++)
                    {
                        if (i == (cl - 1))
                        {
                            ls_item += row[i].ToString() + "\n";
                        }
                        else
                        {
                            ls_item += row[i].ToString() + "\t";
                        }
                    }
                    //HttpContext.Current.Response.Write(ls_item);
                    sw.Write(ls_item);
                    ls_item = "";
                }
                //HttpContext.Current.Response.End();
                sw.Close();
                fileStream.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }