datatable 写入excel 2007

1 添加引用：

NPOI

NPOI.OOXML

2 

        private static void GenerateFile(DataTable dt)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            string s_FileName = "MIR-" + DateTime.Now.ToString("yyyy-MM-dd");
            string  filename = AppDomain.CurrentDomain.BaseDirectory + s_FileName + ".xlsx";
            using (FileStream ms = File.OpenWrite(filename))
            {
                ExportExcel2007(ds, ms);
                //DataToExcel(dt);
            }

            ////upload file to server
            UpLoad(filename);
        }
        public static void ExportExcel2007(DataSet ds, Stream outputStream)
        {
            IWorkbook hssfworkbook = new XSSFWorkbook();

            for (int tableIndex = 0; tableIndex < ds.Tables.Count; tableIndex++)
            {
                DataTable dt = ds.Tables[tableIndex];
                ISheet newSheet = hssfworkbook.CreateSheet(dt.TableName);

                int rowIndex = 0;
                foreach (DataRow drData in dt.Rows)
                {
                    IRow row = newSheet.CreateRow(rowIndex);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (drData[j] != DBNull.Value)
                        {
                            row.CreateCell(j).SetCellValue(drData[j].ToString());
                        }
                    }
                    rowIndex++;
                }
            }

            hssfworkbook.Write(outputStream);
        }

 

转载于:https://www.cnblogs.com/mibing/p/9051506.html