Beispiel #1
0
        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);
        }
Beispiel #2
0
        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();
        }