C# Excel转换为Json
生活随笔
收集整理的這篇文章主要介紹了
C# Excel转换为Json
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
?
?demo：https://files.cnblogs.com/files/guxingy/Excel%E8%BD%AC%E6%8D%A2%E4%B8%BAJson%E5%AF%B9%E8%B1%A1.rar
不太會用博客
?
待轉換的Excel格式
?
轉換后的JSON格式：
?
?Excel轉換類：
public class OperationExcel { /// <summary>/// Excel 轉換為 Datatable/// </summary>/// <param name="sheetNum">工作表索引</param>/// <param name="isFirstRowColumn">首行為列</param>/// <param name="fileName">Excel文件路徑</param>/// <returns></returns>public DataTable ExcelToDataTable(int sheetNum, bool isFirstRowColumn, string fileName){IWorkbook workbook = null;ISheet sheet = null;DataTable myTable = new DataTable();try{var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);if (fileName.IndexOf(".xlsx") > 0)workbook = new XSSFWorkbook(fs);else if (fileName.IndexOf(".xls") > 0)workbook = new HSSFWorkbook(fs);sheet = workbook.GetSheetAt(sheetNum);//工作表不能為空if (sheet == null){string str = "";for (int i = 0; i < workbook.NumberOfSheets; i++){str += workbook.GetSheetAt(i).SheetName + ",";}str = workbook.NumberOfSheets + str;throw new Exception($"sheet不能為空！參數：{sheetNum} 工作簿信息：{str}");}//Excel最大列數int MaxColumnNum = 0;for (int i = 0; i < sheet.LastRowNum; i++){var row = sheet.GetRow(i);if (row.LastCellNum > MaxColumnNum){MaxColumnNum = row.LastCellNum;}}//Excel行數int MaxRowNum = sheet.LastRowNum;//table新增列for (int i = 0; i < MaxColumnNum; ++i){//首行為列if (isFirstRowColumn){bool addEmptyCell = true;//是否添加空列ICell cell = sheet.GetRow(0).GetCell(i);if (cell != null){//table列賦值string cellValue = "";//列名if (cell.CellType == CellType.Numeric){cellValue = cell.NumericCellValue.ToString();}else{cellValue = cell.StringCellValue;}if (!string.IsNullOrWhiteSpace(cellValue)){//列數據為Excel的數據addEmptyCell = false;myTable.Columns.Add(new DataColumn(cellValue));}}if (addEmptyCell){myTable.Columns.Add(new DataColumn(""));//列數據為空 }}else{myTable.Columns.Add(new DataColumn(i + ""));}}//起始行int startRow = 0;if (isFirstRowColumn){startRow = 1;}//DataTable賦值for (int i = startRow; i <= MaxRowNum; ++i){IRow row = sheet.GetRow(i);if (row == null) continue;DataRow NewRow = myTable.NewRow();for (int j = row.FirstCellNum; j < row.LastCellNum; ++j){ICell cell = row.GetCell(j);string value = "";if (cell != null){//table行賦值 if (cell.CellType == CellType.Numeric){value = cell.NumericCellValue.ToString();if ((j == 0) && ((i == 6) || (i == 12))){//特殊的幾個單元格 轉換為 日期格式value = ToDateTimeValue(cell.NumericCellValue.ToString());}}else{//row.GetCell(j).SetCellType(CellType.String);value = cell.StringCellValue;}}NewRow[j] = value;}myTable.Rows.Add(NewRow);}return myTable;}catch (Exception ex){throw ex;}}/// <summary>/// DataTable 轉換為 Html/// </summary>/// <param name="dt"></param>/// <returns></returns>public string GetHtmlString(DataTable dt){StringBuilder sb = new StringBuilder();sb.Append("<html><head>");sb.Append("<title>Excel轉換為Table</title>");sb.Append("<meta http-equiv='content-type' content='text/html; charset=GB2312'> ");sb.Append("<style type=text/css>");sb.Append("td{font-size: 9pt;border:solid 1 #000000;}");sb.Append("table{padding:3 0 3 0;border:solid 1 #000000;margin:0 0 0 0;BORDER-COLLAPSE: collapse;}");sb.Append("</style>");sb.Append("</head>");sb.Append("<body>");sb.Append("<table cellSpacing='0' cellPadding='0' width ='100%' border='1'>");sb.Append("<tr valign='middle'>");sb.Append("<td><b></b></td>");foreach (DataColumn column in dt.Columns){sb.Append("<td><b><span>" + column.ColumnName + "</span></b></td>");}sb.Append("</tr>");int iColsCount = dt.Columns.Count;int rowsCount = dt.Rows.Count - 1;for (int j = 0; j <= rowsCount; j++){sb.Append("<tr>");sb.Append("<td>" + ((int)(j + 1)).ToString() + "</td>");for (int k = 0; k <= iColsCount - 1; k++){sb.Append("<td>");object obj = dt.Rows[j][k];if (obj == DBNull.Value){obj = " ";//如果是NULL則在HTML里面使用一個空格替換之 }if (obj.ToString() == ""){obj = " ";}string strCellContent = obj.ToString().Trim();sb.Append("<span>" + strCellContent + "</span>");sb.Append("</td>");}sb.Append("</tr>");}sb.Append("</table>");//點擊單元格 輸出 行和列sb.Append("<script src='https://cdn.bootcss.com/jquery/1.12.4/jquery.min.js'></script>");sb.Append("<script type='text/javascript'>");sb.Append("$('table tbody').on('click', 'td', function (e) {");sb.Append("var row = $(this).parent().prevAll().length-1 ;");sb.Append("var column = $(this).prevAll().length-1 ;");sb.Append("var str = 'dt.Rows[' + row + '][' + column + '].ToString()';");sb.Append("console.log(str);alert(str);");sb.Append("});");sb.Append("</script>");sb.Append("</body></html>");return sb.ToString();}/// <summary>/// 數字格式的時間 轉換為 字符串格式的時間/// 數字格式的時間 如： 42095.7069444444/0.650694444444444/// </summary>/// <param name="timeStr">數字,如:42095.7069444444/0.650694444444444</param>/// <returns>日期/時間格式</returns>public string ToDateTimeValue(string strNumber){if (!string.IsNullOrWhiteSpace(strNumber)){Decimal tempValue;//先檢查 是不是數字;if (Decimal.TryParse(strNumber, out tempValue)){//天數,取整int day = Convert.ToInt32(Math.Truncate(tempValue));//這里也不知道為什么. 如果是小于32,則減1,否則減2//日期從1900-01-01開始累加 // day = day < 32 ? day - 1 : day - 2;DateTime dt = new DateTime(1900, 1, 1).AddDays(day < 32 ? (day - 1) : (day - 2));//小時:減掉天數,這個數字轉換小時:(* 24) Decimal hourTemp = (tempValue - day) * 24;//獲取小時數//取整.小時數int hour = Convert.ToInt32(Math.Truncate(hourTemp));//分鐘:減掉小時,( * 60)//這里舍入,否則取值會有1分鐘誤差.Decimal minuteTemp = Math.Round((hourTemp - hour) * 60, 2);//獲取分鐘數int minute = Convert.ToInt32(Math.Truncate(minuteTemp));//秒:減掉分鐘,( * 60)//這里舍入,否則取值會有1秒誤差.Decimal secondTemp = Math.Round((minuteTemp - minute) * 60, 2);//獲取秒數int second = Convert.ToInt32(Math.Truncate(secondTemp));//時間格式:00:00:00string resultTimes = string.Format("{0}:{1}:{2}",(hour < 10 ? ("0" + hour) : hour.ToString()),(minute < 10 ? ("0" + minute) : minute.ToString()),(second < 10 ? ("0" + second) : second.ToString()));if (day > 0)return string.Format("{0} {1}", dt.ToString("yyyy-MM-dd"), resultTimes);elsereturn resultTimes;}}return string.Empty;} } View Code?
轉換的方法
/// <summary> /// excel 轉換為 datatable /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) {OperationExcel _OperationExcel = new OperationExcel();DataTable dt = _OperationExcel.ExcelToDataTable(0, true, "21.xls"); }/// <summary> /// datatable 轉換為 html /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) {OperationExcel _OperationExcel = new OperationExcel();DataTable dt = _OperationExcel.ExcelToDataTable(0, true, "21.xls");textBox1.Text = _OperationExcel.GetHtmlString(dt); }/// <summary> /// datatable 轉換為 json格式 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button3_Click(object sender, EventArgs e) {OperationExcel _OperationExcel = new OperationExcel();List<ExcelInfo> list = new List<ExcelInfo>();list.Add(new ExcelInfo("21.xls", 5));list.Add(new ExcelInfo("22.xls", 5));list.Add(new ExcelInfo("23.xls", 4));list.ForEach(c =>{for (int i = 0; i < c.SheetCount; i++){DataTable dt = _OperationExcel.ExcelToDataTable(i, true, c.FileName);string str = CreateStrObj(dt);string num = c.FileName.Replace(".xls", "");string fileName = $"Y{num}-{i + 1}.txt";System.IO.File.WriteAllText($"txt//{fileName}", str);}});MessageBox.Show("格式轉換完成！"); }public string CreateStrObj(DataTable dt) {ZLInfo _ZLInfo = new ZLInfo();//基本信息_ZLInfo._橋梁名稱 = dt.Rows[0][1].ToString();_ZLInfo._構建類型 = dt.Rows[0][5].ToString();_ZLInfo._構件編號 = dt.Rows[0][8].ToString();_ZLInfo._張拉方式 = dt.Rows[0][11].ToString();_ZLInfo._張拉日期 = "";_ZLInfo._砼設計強度 = dt.Rows[2][1].ToString();//詳情數據List<DetailInfor> list = new List<DetailInfor>();int total = 2;for (int i = 0; i < total; i++){DetailInfor _DetailInfor = new DetailInfor();_DetailInfor._孔道號 = dt.Rows[i * 6 + 5][1].ToString();_DetailInfor._張拉時間 = dt.Rows[5][0].ToString();_DetailInfor._理論伸長量 = dt.Rows[5][14].ToString();_DetailInfor._設計張力 = dt.Rows[5][13].ToString();_DetailInfor._回縮量 = dt.Rows[5][12].ToString();_DetailInfor._張拉力 = "";_DetailInfor._油壓 = dt.Rows[5][11].ToString();_DetailInfor._伸長量 = "";_DetailInfor._伸長量誤差 = "";_DetailInfor._千斤頂編號1 = dt.Rows[5][3].ToString();_DetailInfor._初始油壓1 = dt.Rows[5][5].ToString();_DetailInfor._初始張拉力1 = dt.Rows[6][5].ToString();_DetailInfor._初始伸長量1 = dt.Rows[7][5].ToString();_DetailInfor._2倍初始油壓1 = dt.Rows[5][6].ToString();_DetailInfor._2倍初始張拉力1 = dt.Rows[6][6].ToString();_DetailInfor._2倍初始伸長量1 = dt.Rows[7][6].ToString();_DetailInfor._100油壓1 = dt.Rows[5][8].ToString();_DetailInfor._100張拉力1 = dt.Rows[6][8].ToString();_DetailInfor._100伸長量1 = dt.Rows[7][8].ToString();_DetailInfor._千斤頂編號2 = dt.Rows[8][3].ToString();_DetailInfor._初始油壓2 = dt.Rows[8][5].ToString();_DetailInfor._初始張拉力2 = dt.Rows[9][5].ToString();_DetailInfor._初始伸長量2 = dt.Rows[10][5].ToString();_DetailInfor._2倍初始油壓2 = dt.Rows[8][6].ToString();_DetailInfor._2倍初始張拉力2 = dt.Rows[9][6].ToString();_DetailInfor._2倍初始伸長量2 = dt.Rows[10][6].ToString();_DetailInfor._100油壓2 = dt.Rows[8][8].ToString();_DetailInfor._100張拉力2 = dt.Rows[9][8].ToString();_DetailInfor._100伸長量2 = dt.Rows[10][8].ToString();_DetailInfor._是否合格 = "";list.Add(_DetailInfor);}_ZLInfo._詳情數據 = list;string str = JsonConvert.SerializeObject(_ZLInfo);str = str.Replace("_", "");str = str.Replace("100油壓", "100%油壓").Replace("100張拉力", "100%張拉力").Replace("100伸長量", "100%伸長量");return str; } View Code?
相關的類
public class ExcelInfo {public string FileName { get; set; }public int SheetCount { get; set; }public ExcelInfo(string FileName, int SheetCount){this.FileName = FileName;this.SheetCount = SheetCount;} } View Code public class ZLInfo {public string _橋梁名稱 { get; set; }public string _構建類型 { get; set; }public string _構件編號 { get; set; }public string _張拉方式 { get; set; }public string _張拉日期 { get; set; }public string _砼設計強度 { get; set; }public List<DetailInfor> _詳情數據 { get; set; } } View Code public class DetailInfor {public string _孔道號 { get; set; }public string _張拉時間 { get; set; }public string _理論伸長量 { get; set; }public string _設計張力 { get; set; }public string _回縮量 { get; set; }public string _張拉力 { get; set; }public string _油壓 { get; set; }public string _伸長量 { get; set; }public string _伸長量誤差 { get; set; }public string _千斤頂編號1 { get; set; }public string _初始油壓1 { get; set; }public string _初始張拉力1 { get; set; }public string _初始伸長量1 { get; set; }public string _2倍初始油壓1 { get; set; }public string _2倍初始張拉力1 { get; set; }public string _2倍初始伸長量1 { get; set; }public string _100油壓1 { get; set; }public string _100張拉力1 { get; set; }public string _100伸長量1 { get; set; }public string _千斤頂編號2 { get; set; }public string _初始油壓2 { get; set; }public string _初始張拉力2 { get; set; }public string _初始伸長量2 { get; set; }public string _2倍初始油壓2 { get; set; }public string _2倍初始張拉力2 { get; set; }public string _2倍初始伸長量2 { get; set; }public string _100油壓2 { get; set; }public string _100張拉力2 { get; set; }public string _100伸長量2 { get; set; }public string _是否合格 { get; set; } } View Code?
轉載于:https://www.cnblogs.com/guxingy/p/9449595.html
總結
以上是生活随笔為你收集整理的C# Excel转换为Json的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 微信小程序-设置启动页面
- 下一篇： codevs 3981 动态最大子段和（