C#中几种数据格式保存xls(DataTable 和List 保存xls) DataGridView转DataTable

C#中几种数据格式保存xls(DataTable 和List<JObject> 保存xls)

1.DataTable 保存xls

        /// <summary>
        /// Datable导出成Excel,自定义字段 （NPOI组件）
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file">导出路径(包括文件名与扩展名)</param>
        /// <param name="nameList">需要导出指定字段的对应关系</param>
        ////生成列的中文对应表
        //Hashtable nameList = new Hashtable();
        //nameList.Add("ADID", "广告编码");
        //nameList.Add("ADName", "广告名称");
        //nameList.Add("year", "年");        
        public void TableToExcelByCustom(System.Data.DataTable dt, string file, Hashtable nameList)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
            if (workbook == null) { return; }
            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

            //表头  
            IRow row = sheet.CreateRow(0);
            int x = 0;//列的序号
            for (int i = 0; i < dt.Columns.Count; i++) {
                //原始方法
                //ICell cell = row.CreateCell(i);
                //cell.SetCellValue(dt.Columns[i].ColumnName);

                IDictionaryEnumerator Enum = nameList.GetEnumerator();
                while (Enum.MoveNext()) {
                    if (Enum.Key.ToString().Trim() == dt.Columns[i].ColumnName) {
                        sheet.SetColumnWidth(x, 20 * 256);//设置列宽
                        ICell cell = row.CreateCell(x);
                        cell.SetCellValue(Enum.Value.ToString());
                        x++;
                    }
                }


            }

            //数据  
            for (int i = 0; i < dt.Rows.Count; i++) {
                IRow row1 = sheet.CreateRow(i + 1);
                int y = 0;//列的序号
                for (int j = 0; j < dt.Columns.Count; j++) {
                    //原始方法
                    //ICell cell = row1.CreateCell(j);
                    //cell.SetCellValue(dt.Rows[i][j].ToString());

                    IDictionaryEnumerator Enum = nameList.GetEnumerator();
                    while (Enum.MoveNext()) {
                        if (Enum.Key.ToString().Trim() == dt.Columns[j].ColumnName) {
                            ICell cell = row1.CreateCell(y);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            y++;
                        }
                    }

                }
            }

            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件  
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)) {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

2.List<JObject> 保存xls

      /// <summary>
        /// List导出成Excel,自定义字段 （NPOI组件）
        /// </summary>
        /// <param name="List"></param>
        /// <param name="file">导出路径(包括文件名与扩展名)</param>
        /// <param name="nameList">需要导出指定字段的对应关系</param>
        ////生成列的中文对应表
        //Hashtable nameList = new Hashtable();
        //nameList.Add("ADID", "广告编码");
        //nameList.Add("ADName", "广告名称");
        //nameList.Add("year", "年");        
        public void ListToExcel(List<JObject> list, string file, Hashtable nameList)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
            if (workbook == null) { return; }
            //ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
            ISheet sheet = workbook.CreateSheet("Sheet1");

            //表头
            if (list.Count <= 0) return;//空数据返回
            StringBuilder columns = new StringBuilder();
            JObject listColumns = list[0] as JObject;

            IRow row = sheet.CreateRow(0);
            int x = 0;//列的序号
            foreach (JToken jkon in listColumns.AsJEnumerable()) {
                string column = ((JProperty)(jkon)).Name;//列名

                IDictionaryEnumerator Enum = nameList.GetEnumerator();
                while (Enum.MoveNext()) {
                    if (Enum.Key.ToString().Trim() == column ) {
                        sheet.SetColumnWidth(x, 20 * 256);//设置列宽
                        ICell cell = row.CreateCell(x);
                        cell.SetCellValue(Enum.Value.ToString());
                        x++;
                    }
                }
            }

            //数据  
            for (int i = 0; i < list.Count; i++) {
                IRow row1 = sheet.CreateRow(i + 1);
                int y = 0;//列的序号
                foreach (JToken jkon in listColumns.AsJEnumerable()) {
                    string column = ((JProperty)(jkon)).Name;//列名

                    IDictionaryEnumerator Enum = nameList.GetEnumerator();
                    while (Enum.MoveNext()) {
                        if (Enum.Key.ToString().Trim() == column) {
                            ICell cell = row1.CreateCell(y);
                            cell.SetCellValue(list[i][column].ToString());
                            y++;
                        }
                    }

                }
            }

            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件  
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)) {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

=======

要使用上面两个方法，就必须把数据转成上面指定的格式（DataTable 和List）

1. DataGridView转DataTable

               //先判断DataGridView不能为空
 
                //===DataGridView转DataTable(开始)
                DataTable dt = new DataTable();

                //表头
                for (int count = 0; count < dgv.Columns.Count; count++) {
                    DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
                    dt.Columns.Add(dc);
                }


　　　　　　　　　　//添加数据
                for (int count = 0; count < dgv.RowCount; count++) {
                    DataRow dr = dt.NewRow();
                    for (int countsub = 0; countsub < dgv.Columns.Count; countsub++) {
                        dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
                    }
                    dt.Rows.Add(dr);
                }
                //===DataGridView转DataTable(结束)        

2.List<JObject>转DataTable  （List可以直接存，也可以转成DataTable）

               //先要判断List，不能为空  list是 List<JObject> list = new List<JObject>(); 这样的数据（或是接受的接口json数据）

　　　　　　　　　　//=== List<JObject>转DataTable(开始)
                DataTable dt = new DataTable();
                //把List<JObject>数据，转成DataTable
                StringBuilder columns = new StringBuilder();
                JObject listColumns = list[0] as JObject;

                //构造表头
                foreach (JToken jkon in listColumns.AsJEnumerable())
                {
                    string column_name = ((JProperty)(jkon)).Name;
                    DataColumn dc = new DataColumn(column_name);
                    dt.Columns.Add(dc);
                }

　　　　　　　　　　//添加数据
                for (int count = 0; count < list.Count; count++) {
                    DataRow dr = dt.NewRow();
                    foreach (JToken jkon in listColumns.AsJEnumerable()) {
                        string column_name = ((JProperty)(jkon)).Name;
                        dr[column_name] = Convert.ToString(list[count][column_name].ToString());
                    }
                    dt.Rows.Add(dr);
                }
                //=== List<JObject>转DataTable(结束)

参考： https://www.csdn.net/tags/MtTaEgxsMDIwODA1LWJsb2cO0O0O.html