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
