JsonToExcel：

1   public static void JsonToExcel(List
     
      
       > json, string fileName) 2         { 3             using (MemoryStream ms = new MemoryStream()) 4             { 5  6                 IWorkbook workbook = null; 7  8                 if (fileName.IndexOf(".xlsx") > 0) 9                     workbook = new XSSFWorkbook();10                 if (fileName.IndexOf(".xls") > 0)11                     workbook = new HSSFWorkbook();12                 ISheet sheet = workbook.CreateSheet();13                 IRow headerRow = sheet.CreateRow(0);14 15                 //// handling header.  16                 //foreach (DataColumn column in table.Columns)17                 //    headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value  18 19 20                 var keys = json.FirstOrDefault().Keys.ToList();21                 for (var i = 0; i < keys.Count(); i++)22                 {23                     headerRow.CreateCell(i).SetCellValue(keys[i]);24                 }25 26                 // handling value.  27                 int rowIndex = 1;28                 29                 for (var i = 0; i < json.Count(); i++)30                 {31                     IRow dataRow = sheet.CreateRow(rowIndex);32 33                     var values=json[i].Values.ToList();34                     for (var j = 0; j 
      
     

 

 

 

 

DataTableToExcel:

1  public static void dataTableToExcel(DataTable table, string fileName) 2         { 3             using (MemoryStream ms = new MemoryStream()) 4             { 5  6                 IWorkbook workbook = null; 7  8                 if (fileName.IndexOf(".xlsx") > 0) 9                     workbook = new XSSFWorkbook();10                 if (fileName.IndexOf(".xls") > 0)11                     workbook = new HSSFWorkbook();12                 ISheet sheet = workbook.CreateSheet();13                 IRow headerRow = sheet.CreateRow(0);14 15                 // handling header.  16                 foreach (DataColumn column in table.Columns)17                     headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value  18 19                 // handling value.  20                 int rowIndex = 1;21 22                 foreach (DataRow row in table.Rows)23                 {24                     IRow dataRow = sheet.CreateRow(rowIndex);25 26                     foreach (DataColumn column in table.Columns)27                     {28                         dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());29                     }30 31                     rowIndex++;32                 }33 34                 workbook.Write(ms);35                 ms.Flush();36 37                 using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))38                 {39                     byte[] data = ms.ToArray();40 41                     fs.Write(data, 0, data.Length);42                     fs.Flush();43 44                     data = null;45                 }46             }47         }

ExcelToDataTable:

/// 
        /// 将excel中的数据导入到DataTable中        /// 
        /// excel工作薄sheet的名称        /// 第一行是否是DataTable的列名        /// 
      
       返回的DataTable
              public static DataTable ExcelToDataTable(string filePath, bool isColumnName, int sheetName)        {            DataTable dataTable = null;            FileStream fs = null;            DataColumn column = null;            DataRow dataRow = null;            IWorkbook workbook = null;            ISheet sheet = null;            IRow row = null;            ICell cell = null;            int startRow = 0;            try            {                using (fs = File.OpenRead(filePath))                {                    // 2007版本                    if (filePath.IndexOf(".xlsx") > 0)                        workbook = new XSSFWorkbook(fs);                    // 2003版本                    else if (filePath.IndexOf(".xls") > 0)                        workbook = new HSSFWorkbook(fs);                    if (workbook != null)                    {                        sheet = workbook.GetSheetAt(sheetName);//读取第一个sheet，当然也可以循环读取每个sheet                        dataTable = new DataTable();                        if (sheet != null)                        {                            int rowCount = sheet.LastRowNum;//总行数                            if (rowCount > 0)                            {                                IRow firstRow = sheet.GetRow(0);//第一行                                int cellCount = firstRow.LastCellNum;//列数                                //构建datatable的列                                if (isColumnName)                                {                                    startRow = 1;//如果第一行是列名，则从第二行开始读取                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                                    {                                        cell = firstRow.GetCell(i);                                        if (cell != null)                                        {                                            if (cell.StringCellValue != null)                                            {                                                column = new DataColumn(cell.StringCellValue);                                                dataTable.Columns.Add(column);                                            }                                        }                                    }                                }                                else                                {                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)                                    {                                        column = new DataColumn("column" + (i + 1));                                        dataTable.Columns.Add(column);                                    }                                }                                //填充行                                for (int i = startRow; i <= rowCount; ++i)                                {                                    row = sheet.GetRow(i);                                    if (row == null) continue;                                    dataRow = dataTable.NewRow();                                    for (int j = row.FirstCellNum; j < cellCount; ++j)                                    {                                        cell = row.GetCell(j);                                        if (cell == null)                                        {                                            dataRow[j] = "";                                        }                                        else                                        {                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)                                            switch (cell.CellType)                                            {                                                case CellType.Blank:                                                    dataRow[j] = "";                                                    break;                                                case CellType.Numeric:                                                    short format = cell.CellStyle.DataFormat;                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理                                                    if (format == 14 || format == 31 || format == 57 || format == 58)                                                        dataRow[j] = cell.DateCellValue;                                                    else                                                        dataRow[j] = cell.NumericCellValue;                                                    break;                                                case CellType.String:                                                    dataRow[j] = cell.StringCellValue;                                                    break;                                            }                                        }                                    }                                    dataTable.Rows.Add(dataRow);                                }                            }                        }                    }                }                return dataTable;            }            catch (Exception ex)            {                Console.WriteLine("exception:" + ex.ToString());                Console.ReadLine();                if (fs != null)                {                    fs.Close();                }                return null;            }        }