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