发布：2024/4/2 13:27:47作者：管理员 来源：本站 浏览次数：455

1、将Excel导入成DataTable

       使用NPOI的程序集

   using NPOI.HSSF.UserModel;

   using NPOI.SS.UserModel;

   using NPOI.XSSF.UserModel;

   /// <summary>

   /// Excel导入成Datable

   /// </summary>

   /// <param name="file">导入路径(包含文件名与扩展名)</param>

   /// <returns></returns>

   public static DataTable ExcelToTable(string file)

   {

       try

       {

           DataTable dt = new DataTable();

           IWorkbook workbook;

           string fileExt = Path.GetExtension(file).ToLower();

           using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))

           {

               //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式

               if (fileExt == ".xlsx")

               {

                   workbook = new XSSFWorkbook(fs);

               }

               else if (fileExt == ".xls")

               {

                   workbook = new HSSFWorkbook(fs);

               }

               else { workbook = null; }

               if (workbook == null) { return null; }

               ISheet sheet = workbook.GetSheetAt(0);

               //表头  

               IRow header = sheet.GetRow(sheet.FirstRowNum);

               List<int> columns = new List<int>();

               for (int i = 0; i < header.LastCellNum; i++)

               {

                   object obj = GetValueType(header.GetCell(i));

                   if (obj == null || obj.ToString() == string.Empty)

                   {

                       dt.Columns.Add(new DataColumn("Columns" + i.ToString()));

                   }

                   else

                       dt.Columns.Add(new DataColumn(obj.ToString()));

                   columns.Add(i);

               }

               //数据  

               for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)

               {

                   DataRow dr = dt.NewRow();

                   bool hasValue = false;

                   foreach (int j in columns)

                   {

                       dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));

                       if (dr[j] != null && dr[j].ToString() != string.Empty)

                       {

                           hasValue = true;

                       }

                   }

                   if (hasValue)

                   {

                       dt.Rows.Add(dr);

                   }

               }

           }

           return dt;

       }

       catch (Exception ex)

       {

           throw ex;

       }

   }

2、Excel导入成Datable，无表头

   /// <summary>

   /// Excel导入成Datable,无表头

   /// </summary>

   /// <param name="file">导入路径(包含文件名与扩展名)</param>

   /// <returns></returns>

   public static DataTable ExcelToTableData(string file)

   {

       try

       {

           DataTable dt = new DataTable();

           IWorkbook workbook;

           string fileExt = Path.GetExtension(file).ToLower();

           using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))

           {

               //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式

               if (fileExt == ".xlsx")

               {

                   workbook = new XSSFWorkbook(fs);

               }

               else if (fileExt == ".xls")

               {

                   workbook = new HSSFWorkbook(fs);

               }

               else { workbook = null; }

               if (workbook == null) { return null; }

               ISheet sheet = workbook.GetSheetAt(0);

               //表头  

               IRow header = sheet.GetRow(sheet.FirstRowNum);

               List<int> columns = new List<int>();

               for (int i = 0; i < header.LastCellNum; i++)

               {

                   dt.Columns.Add(new DataColumn("Columns" + i.ToString()));

                   columns.Add(i);

               }

               //数据  

               for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)

               {

                   DataRow dr = dt.NewRow();

                   bool hasValue = false;

                   foreach (int j in columns)

                   {

                       dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));

                       if (dr[j] != null && dr[j].ToString() != string.Empty)

                       {

                           hasValue = true;

                       }

                   }

                   if (hasValue)

                   {

                       dt.Rows.Add(dr);

                   }

               }

           }

           return dt;

       }

       catch (Exception ex)

       {

           throw ex;

       }

   }

3、将DataTable导出成Excel

   /// <summary>

   /// Datable导出成Excel

   /// </summary>

   /// <param name="dt"></param>

   /// <param name="file">导出路径(包括文件名与扩展名)</param>

   public static void TableToExcel(DataTable dt, string file)

   {

       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);

       for (int i = 0; i < dt.Columns.Count; i++)

       {

           ICell cell = row.CreateCell(i);

           cell.SetCellValue(dt.Columns[i].ColumnName);

       }

       //数据  

       for (int i = 0; i < dt.Rows.Count; i++)

       {

           IRow row1 = sheet.CreateRow(i + 1);

           for (int j = 0; j < dt.Columns.Count; j++)

           {

               ICell cell = row1.CreateCell(j);

               cell.SetCellValue(dt.Rows[i][j].ToString());

           }

       }

       //转为字节数组  

       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();

       }

   }

4、获取单元格类型

   /// <summary>

   /// 获取单元格类型

   /// </summary>

   /// <param name="cell"></param>

   /// <returns></returns>

   private static object GetValueType(ICell cell)

   {

       if (cell == null)

           return null;

       switch (cell.CellType)

       {

           case CellType.Blank: //BLANK:  

               return null;

           case CellType.Boolean: //BOOLEAN:  

               return cell.BooleanCellValue;

           case CellType.Numeric: //NUMERIC:  

               return cell.NumericCellValue;

           case CellType.String: //STRING:  

               return cell.StringCellValue;

           case CellType.Error: //ERROR:  

               return cell.ErrorCellValue;

           case CellType.Formula: //FORMULA:  

           default:

               return "=" + cell.CellFormula;

       }

   }

5、DataSet转Json

   public static string DataSetToJson(DataSet metaData)

   {

       StringBuilder sb = new StringBuilder();

       sb.Append("{");

       JavaScriptSerializer serializer = new JavaScriptSerializer();

       foreach (DataTable dt in metaData.Tables)

       {

           sb.Append(string.Format("\"{0}\":", dt.TableName));

           ArrayList arrayList = new ArrayList();

           foreach (DataRow dataRow in dt.Rows)

           {

               Dictionary<string, object> dictionary = new Dictionary<string, object>();

               foreach (DataColumn dataColumn in dt.Columns)

               {

                   dictionary.Add(dataColumn.ColumnName, dataRow[dataColumn.ColumnName]);

               }

               arrayList.Add(dictionary);

           }

           sb.Append(serializer.Serialize(arrayList));

           sb.Append(",");

       }

       return sb.Remove(sb.Length - 1, 1).Append("}").ToString();

   }

第二种，通过Newtonsoft.Json库来完成

       public static void Main()  

       {  

           // 创建一个简单的DataSet  

           DataSet dataSet = new DataSet();  

           DataTable dataTable = new DataTable("Table1");  

           dataTable.Columns.Add("Column1", typeof(string));  

           dataTable.Columns.Add("Column2", typeof(int));  

           dataTable.Rows.Add("Row1", 1);  

           dataTable.Rows.Add("Row2", 2);  

           dataSet.Tables.Add(dataTable);  

           // 将DataSet转换为JSON字符串  

           string jsonString = JsonConvert.SerializeObject(dataSet, Formatting.Indented);  

           Console.WriteLine(jsonString);  

       }  

6、DataTable转为实体类集合，使用泛型

   //使用new泛型约束，T要有一个无参数构造函数

   public static class ConvertHelper<T> where T : new()

   {

       public static List<T> ConvertToModel(DataTable dt)

       {

           //定义集合

           List<T> ts = new List<T>();

           T t = new T();

           string tempName = string.Empty;

           //获取此模型的公共属性

           PropertyInfo[] propertys = t.GetType().GetProperties();

           foreach (DataRow row in dt.Rows)

           {

               t = new T();

               foreach (PropertyInfo pi in propertys)

               {

                   tempName = pi.Name;

                   //检查DataTable是否包含此列

                   if (dt.Columns.Contains(tempName))

                   {

                       //判断此属性是否有set

                       if (!pi.CanWrite)

                           continue;

                       object value = row[tempName];

                       if (value != DBNull.Value)

                           pi.SetValue(t, value, null);

                   }

               }

               ts.Add(t);

           }

           return ts;

       }  

   }

7、将DataRow[]转为DataTable

   public static DataTable DataRowToDataTable(DataRow[] rows)

   {

       if (rows == null || rows.Length == 0) return null;

       DataTable tmp = rows[0].Table.Clone(); // 复制DataRow的表结构

       foreach (DataRow row in rows)

       {

           tmp.ImportRow(row); // 将DataRow添加到DataTable中

       }

       return tmp;

   }

8、将DataRow转为JSON字符串

   public static string DataRowToJson(DataRow dr)

   {

       JObject jObj = new JObject();

       foreach (DataColumn item in dr.Table.Columns)

       {

           jObj.Add(new JProperty(item.ColumnName, dr[item.ColumnName]));

       }

       return jObj.ToString();

   }

9、JSON转为DataTable

   public static DataTable JsonToDataTable(string strJson)

   {

       //转换json格式

       strJson = strJson.Replace(",\"", "*\"").Replace("\":", "\"#").ToString();

       //取出表名

       var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase);

       string strName = rg.Match(strJson).Value;

       DataTable tb = null;

       //去除表名

       strJson = strJson.Substring(strJson.IndexOf("[") + 1);

       strJson = strJson.Substring(0, strJson.IndexOf("]"));

       //获取数据

       rg = new Regex(@"(?<={)[^}]+(?=})");

       MatchCollection mc = rg.Matches(strJson);

       for (int i = 0; i < mc.Count; i++)

       {

           string strRow = mc[i].Value;

           string[] strRows = strRow.Split('*');

           //创建表

           if (tb == null)

           {

               tb = new DataTable();

               tb.TableName = strName;

               foreach (string str in strRows)

               {

                   var dc = new DataColumn();

                   string[] strCell = str.Split('#');

                   if (strCell[0].Substring(0, 1) == "\"")

                   {

                       int a = strCell[0].Length;

                       dc.ColumnName = strCell[0].Substring(1, a - 2);

                   }

                   else

                   {

                       dc.ColumnName = strCell[0];

                   }

                   tb.Columns.Add(dc);

               }

               tb.AcceptChanges();

           }

           //增加内容

           DataRow dr = tb.NewRow();

           for (int r = 0; r < strRows.Length; r++)

           {

               dr[r] = strRows[r].Split('#')[1].Trim().Replace("，", ",").Replace("：", ":").Replace("\"", "");

           }

           tb.Rows.Add(dr);

           //提交自上次调用 System.Data.DataTable.AcceptChanges 以来对该表进行的所有更改。

           tb.AcceptChanges();

       }

       return tb;

   }

第二种方法

   using Newtonsoft.Json;  

   using System.Data;  

   public DataTable ConvertJsonToDataTable(string json)  

   {  

       DataTable dt = JsonConvert.DeserializeObject<DataTable>(json);  

       return dt;  

   }

       请注意，这个函数假设你的JSON字符串直接表示一个表格（例如，它是从一个数据库查询的结果中生成的）。如果你的JSON字符串表示的是其他结构（例如，一个包含多个对象的数组），你可能需要稍微修改这个函数以正确地解析你的数据。

10、使用System.Data.OleDb程序集读取Excel文件到DataSet

       该方式需要通过类似于数据库的连接串来连接Excel程序，因此需要本地安装有Excel程序才能使用。

   //将excel数据写入dataset

   public static DataSet loadDatesetFromExcel(string filepath)

   {

       string strConn;

       switch (filepath.Split('.').Last())

       {

           case "xls":    //X86

               strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";

               break;

           case "xlsx":   //X64

               strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=false;IMEX=1'";

               break;

           default:

               strConn = "";

               break;

       }

       if (strConn != "")

       {

           try

           {

               string sheetname = filepath.Split('.').First().Split('\\').Last();

               OleDbConnection OleConn = new OleDbConnection(string.Format(strConn));

               OleConn.Open();

               string sql = "SELECT * FROM [" + sheetname + "$]";

               OleDbDataAdapter OLeDaExcel = new OleDbDataAdapter(sql, OleConn);

               DataSet ds = new DataSet();

               OLeDaExcel.Fill(ds, "Sheet1");

               OleConn.Close();

               return ds;

           }

           catch (Exception ex)

           {

               return null;

           }

       }

       else

       {

           return null;

       }

   }