public static string ParseAndWriteToJson(DataTable dataTable) { List <ItemRow> rows = new List <ItemRow>(); try { foreach (DataRow dataRow in dataTable.Rows) { ItemRow row = new ItemRow { FILIAL = dataRow["FILIAL"].ToString(), CASHID = dataRow["CASHID"].ToString(), CASHNAME = dataRow["CASHNAME"].ToString(), SHORTNAME = dataRow["SHORTNAME"].ToString(), FULLNAME = dataRow["FULLNAME"].ToString(), DEPNUM = dataRow["DEPNUM"].ToString(), DEPNAME = dataRow["DEPNAME"].ToString(), DCODE = dataRow["DCODE"].ToString(), DNAME = dataRow["DNAME"].ToString(), DOCTPOST_ID = dataRow["DOCTPOST_ID"].ToString(), DOCTPOST = dataRow["DOCTPOST"].ToString(), WDATE = dataRow["WDATE"].ToString(), STARTTIME = dataRow["STARTTIME"].ToString(), ENDTIME = dataRow["ENDTIME"].ToString(), INTERVALTIME = dataRow["INTERVALTIME"].ToString() }; rows.Add(row); } } catch (Exception) { } string json = Newtonsoft.Json.JsonConvert.SerializeObject(rows, Newtonsoft.Json.Formatting.Indented); string resultFile = ExcelGeneral.GetResultFilePath("TimetableToSite", isPlainText: true).Replace(".txt", ".json"); try { File.WriteAllText(resultFile, json); } catch (Exception e) { Logging.ToLog(e.Message + Environment.NewLine + e.StackTrace); } return(resultFile); }
private static void FssInfoAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Cells[3, 1].Select(); string pivotTableName = @"FssInfoPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; int wsRowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.Range["A2:BG" + wsRowsUsed], 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Год").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Год").Position = 1; //pivotTable.PivotFields("Год").AutoSort(Excel.XlSortOrder.xlDescending, "Год"); pivotTable.PivotFields("Квартал").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Квартал").Position = 2; //pivotTable.PivotFields("Квартал").AutoSort(Excel.XlSortOrder.xlDescending, "Квартал"); pivotTable.PivotFields("Месяц").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Месяц").Position = 3; //pivotTable.PivotFields("Месяц").AutoSort(Excel.XlSortOrder.xlDescending, "Месяц"); pivotTable.PivotFields("Неделя").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Неделя").Position = 4; //pivotTable.PivotFields("Неделя").AutoSort(Excel.XlSortOrder.xlDescending, "Неделя"); pivotTable.AddDataField(pivotTable.PivotFields("Номер ЛН"), "Кол-во Номер ЛН", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Адрес").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Адрес").Position = 1; pivotTable.HasAutoFormat = false; int wsPivoteColumnUsed = wsPivote.UsedRange.Columns.Count; wsPivote.Columns["B:" + ExcelGeneral.ColumnIndexToColumnLetter(wsPivoteColumnUsed)].Select(); xlApp.Selection.ColumnWidth = 6; wsPivote.Range["B2:" + ExcelGeneral.ColumnIndexToColumnLetter(wsPivoteColumnUsed) + "5"].Select(); xlApp.Selection.HorizontalAlignment = Excel.Constants.xlGeneral; xlApp.Selection.VerticalAlignment = Excel.Constants.xlTop; xlApp.Selection.WrapText = true; pivotTable.PivotFields("Месяц").ShowDetail = false; pivotTable.TableStyle2 = "PivotStyleMedium13"; pivotTable.PivotFields("Неделя").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Месяц").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Квартал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; //pivotTable.PivotFields("Год").PivotItems("2017").Visible = false; //pivotTable.PivotFields("Год").PivotItems("2018").Visible = false; //pivotTable.PivotFields("Адрес").PivotItems("г.Москва, Вадковский переулок, д.18").Visible = false; pivotTable.DisplayFieldCaptions = false; wb.ShowPivotTableFieldList = false; wsPivote.Range["A1"].Select(); }