protected void Button_ExportIntoExcel_Click(object sender, EventArgs e) { //点击此按钮导出到Excel ExcelManipulation exc = new ExcelManipulation(); string _year = DateTime.Now.Year + ""; string _month = DateTime.Now.Month + ""; string _day = DateTime.Now.Day + ""; string _hour = DateTime.Now.Hour + ""; string _minute = DateTime.Now.Minute + ""; string _second = DateTime.Now.Second + ""; string _miliSecond = DateTime.Now.Millisecond + ""; string fileName = DropDownList_Grade.SelectedItem.Text + _year + "年" + _month + "月" + _day + "日" + _hour + "时" + _minute + "分" + _second + "秒" + _miliSecond; exc.setFileName(fileName); for (int i = 0; i < DropDownList_class.Items.Count; i++) { ListItem item = DropDownList_class.Items[i]; string SheetName = item.Text.ToString().Substring(0, 2); //string SheetName = "Table_" + item.Text.ToString().Substring(0,2) + "_" + _year + "_" + _month + "_" + _day + "_" + _hour + "_" + _minute + "_" + _second; string sql_createTable = "create table " + SheetName + " ([节次] VarChar,[星期一] VarChar,[星期二] VarChar,[星期三] VarChar,[星期四] VarChar,[星期五] VarChar)"; exc.ExecuteNonQuery(sql_createTable, null); //创建一个表单 string [] class_1 = ProvideLessonDataByClass(DropDownList_Grade.SelectedValue, item.Value); for (int lesson = 1; lesson <= 8; lesson++) { string lessonNo = "第" + lesson + "节"; string[] subjectName = new string[6]; for (int day = 1; day <= 5; day++) { //string subjectName = class_1[lesson + (day-1) * 8]; subjectName[day] = class_1[lesson + (day - 1) * 8]; } // cmd.CommandText = "INSERT INTO TestSheet VALUES(1,'elmer','password')"; string sql_insert = "insert into " + SheetName + " values( '" + lessonNo + "','" + subjectName[1] + "','" + subjectName[2] + "','" + subjectName[3] + "','" + subjectName[4] + "','" + subjectName[5] + "')"; exc.ExecuteNonQuery(sql_insert, null); } } System.Web.UI.ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "Button6_Click", "alert('年级课表已导出到Excel文件中,位于D盘根目录。')", true); }
/// <summary> /// Given a filePath, it will create an excel file which will contain the outputs of the given Reports. /// </summary> /// <param name="filePath"></param> /// <param name="reportList"></param> private void queryReportsToSpreadsheet(string filePath, List <Report> reportList) { Utility.WriteToConsole(Constants.creatExcelDocMessage, 7); char[] charactersToRemove = { '[', ']' }; // Setup Summery string summarySpreadSheetName = "Summary"; ExcelManipulation.CreateExcelFile(filePath, summarySpreadSheetName); int percentageStyle = ExcelManipulation.GetStyleIndexOfDefaultPercentageFormatStyle(filePath); int commaNumberStyle = ExcelManipulation.GetStyleIndexOfDefaultCommaNumberFormatStyle(filePath); int startSummaryTable = 3; int endSummaryTable = startSummaryTable + 1; // Setup variables for inputing List <List <string> > summaryNumberBlock = new List <List <string> >(); List <int> summaryStyles = new List <int>(); List <List <string> > summaryTextBlock = new List <List <string> >(); List <string> summaryReport = new List <string>(); List <string> summaryMeasure = new List <string>(); List <string> summarySlicer = new List <string>(); List <string> summaryMember = new List <string>(); List <string> summaryValue1 = new List <string>(); List <string> summaryValue2 = new List <string>(); List <string> summaryDifference = new List <string>(); List <string> summaryPercentDifference = new List <string>(); List <string> summaryHeader = new List <string>(); summaryHeader.Add(Constants.report); summaryHeader.Add(Constants.measure); summaryHeader.Add(Constants.dimensionAttribute); summaryHeader.Add(Constants.memberRowLabels); summaryHeader.Add(string.Format("{0} {1} {2}", Cube1.Datasource, Cube1.Catalog, Cube1.Cube)); summaryHeader.Add(string.Format("{0} {1} {2}", Cube2.Datasource, Cube2.Catalog, Cube2.Cube)); summaryHeader.Add(Constants.difference); summaryHeader.Add(Constants.percentDifference); foreach (Report report in reportList) { // Create a spreadsheet Utility.WriteToConsole(string.Format(Constants.creatingSpreadsheet, report.ReportNumber), 5); string spreadSheetName = string.Format(Constants.reportNumber, report.ReportNumber); string titleMeasure = string.Join("", report.MeasureUniqueName.Split('.')[1].Split(charactersToRemove)); string titleSlicer = string.Join("", (report.SliceByAttribute.Split('.')[0] + " " + report.SliceByAttribute.Split('.')[1]).Split(charactersToRemove)); string titleFilter = Constants.filter; string titleMDXQuery = string.Format(Constants.mdxQuery, getReportsCommand(report, Cube1)); int tableRowStart = 5; int dataStyle; List <string> headers = new List <string>(); headers.Add(report.SliceByAttribute); headers.Add(string.Format("{0} {1} {2}", Cube1.Datasource, Cube1.Catalog, Cube1.Cube)); headers.Add(string.Format("{0} {1} {2}", Cube2.Datasource, Cube2.Catalog, Cube2.Cube)); headers.Add(Constants.difference); headers.Add(Constants.percentDifference); foreach (string s in report.FilterList) { titleFilter += s + ", "; } ExcelManipulation.CreateSpreadsheet(filePath, spreadSheetName); ExcelManipulation.InsertText(filePath, spreadSheetName, 1, Constants.A, titleMeasure); ExcelManipulation.InsertText(filePath, spreadSheetName, 2, Constants.A, titleSlicer); ExcelManipulation.InsertText(filePath, spreadSheetName, 3, Constants.A, titleFilter); ExcelManipulation.InsertText(filePath, spreadSheetName, 4, Constants.A, titleMDXQuery); ExcelManipulation.InsertRowText(filePath, spreadSheetName, (uint)tableRowStart, Constants.A, headers); ExcelManipulation.AddPercentageFormatColumn(filePath, spreadSheetName, 5); // Must be done before adding new values. // Query both cubes Utility.WriteToConsole(string.Format(Constants.query, report.MeasureUniqueName, report.SliceByAttribute, Cube1.Datasource, Cube1.Catalog, Cube1.Cube), 3); Dictionary <string, double> results1 = GetSlicedMeasureData(Cube1, getReportsCommand(report, Cube1)); Utility.WriteToConsole(string.Format(Constants.query, report.MeasureUniqueName, report.SliceByAttribute, Cube2.Datasource, Cube2.Catalog, Cube2.Cube), 3); Dictionary <string, double> results2 = GetSlicedMeasureData(Cube2, getReportsCommand(report, Cube2)); List <string> allMembers = results1.Keys.Union(results2.Keys).ToList(); // Insert data into spreadsheet Utility.WriteToConsole(string.Format(Constants.insertingData, report.ReportNumber), 3); List <string> cube1Data = new List <string>(); List <string> cube2Data = new List <string>(); List <string> differenceData = new List <string>(); List <string> percentageData = new List <string>(); bool isDataAPercentage = false; for (int i = 0; i < allMembers.Count; i++) { double value_1 = 0; double value_2 = 0; double percentDifference = 0; double difference = 0; if (results1.ContainsKey(allMembers[i])) { value_1 = results1[allMembers[i]]; } if (results2.ContainsKey(allMembers[i])) { value_2 = results2[allMembers[i]]; } difference = value_2 - value_1; if (value_1 == 0) { if (value_2 != 0) { percentDifference = 1; } } else { percentDifference = Math.Abs(difference) / value_1; } cube1Data.Add(Convert.ToString(value_1)); cube2Data.Add(Convert.ToString(value_2)); differenceData.Add(Convert.ToString(difference)); percentageData.Add(Convert.ToString(percentDifference)); // Check if data passes the report's threshold if (report.Threshold.doesDataPassThreshold(allMembers[i], value_1, value_2, difference, percentDifference)) { summaryReport.Add(string.Format(Constants.report, report.ReportNumber)); summaryMeasure.Add(titleMeasure); summarySlicer.Add(titleSlicer); summaryMember.Add(allMembers[i]); summaryValue1.Add(value_1.ToString()); summaryValue2.Add(value_2.ToString()); summaryDifference.Add(difference.ToString()); summaryPercentDifference.Add(percentDifference.ToString()); } // Check if any of the data is a percentage to determine how to format it. if (value_1 != 0 && value_1 < 1) { isDataAPercentage = true; } } // Insert all data, based on the determined format. if (isDataAPercentage) { dataStyle = percentageStyle; } else { dataStyle = commaNumberStyle; } List <List <string> > memberBlock = new List <List <string> >(); memberBlock.Add(allMembers); List <List <string> > numberBlock = new List <List <string> >(); numberBlock.Add(cube1Data); numberBlock.Add(cube2Data); numberBlock.Add(differenceData); numberBlock.Add(percentageData); List <int> styles = new List <int>(); styles.Add(dataStyle); styles.Add(dataStyle); styles.Add(dataStyle); styles.Add(percentageStyle); ExcelManipulation.InsertBlockTextByColumn(filePath, spreadSheetName, (uint)tableRowStart + 1, Constants.A, memberBlock); ExcelManipulation.InsertBlockOfNumbersByColumns(filePath, spreadSheetName, (uint)tableRowStart + 1, Constants.B, numberBlock, styles); // Format spreadsheet Utility.WriteToConsole(string.Format(Constants.formattingReport, report.ReportNumber), 3); ExcelManipulation.AddConditionalFormattingColorScale(filePath, spreadSheetName, 2, Constants.ee); // Note that due to how the OpenXML standard requires specific ordering for XML items, Conditional formatting must be added before a table. ExcelManipulation.AddTableToSpreadsheet(filePath, spreadSheetName, headers, string.Format(Constants.tableRange, (uint)tableRowStart, (allMembers.Count + (uint)tableRowStart).ToString()), string.Format(Constants.tableName, report.ReportNumber)); } // Create summary Utility.WriteToConsole(Constants.creatingSummary, 3); ExcelManipulation.InsertText(filePath, summarySpreadSheetName, 1, Constants.A, Constants.cube1); ExcelManipulation.InsertText(filePath, summarySpreadSheetName, 1, Constants.B, string.Format("{0} {1} {2}", Cube1.Datasource, Cube1.Catalog, Cube1.Cube)); ExcelManipulation.InsertText(filePath, summarySpreadSheetName, 2, Constants.A, Constants.cube2); ExcelManipulation.InsertText(filePath, summarySpreadSheetName, 2, Constants.B, string.Format("{0} {1} {2}", Cube2.Datasource, Cube2.Catalog, Cube2.Cube)); summaryTextBlock.Add(summaryReport); summaryTextBlock.Add(summaryMeasure); summaryTextBlock.Add(summarySlicer); summaryTextBlock.Add(summaryMember); summaryNumberBlock.Add(summaryValue1); summaryNumberBlock.Add(summaryValue2); summaryNumberBlock.Add(summaryDifference); summaryNumberBlock.Add(summaryPercentDifference); summaryStyles.Add(-1); summaryStyles.Add(-1); summaryStyles.Add(-1); summaryStyles.Add(percentageStyle); ExcelManipulation.InsertRowText(filePath, summarySpreadSheetName, (uint)startSummaryTable, Constants.A, summaryHeader); ExcelManipulation.InsertBlockTextByColumn(filePath, summarySpreadSheetName, (uint)startSummaryTable + 1, Constants.A, summaryTextBlock); ExcelManipulation.InsertBlockOfNumbersByColumns(filePath, summarySpreadSheetName, (uint)startSummaryTable + 1, Constants.E, summaryNumberBlock, summaryStyles); if (summaryReport.Count() > 0) { endSummaryTable = startSummaryTable + summaryReport.Count(); } ExcelManipulation.AddTableToSpreadsheet(filePath, summarySpreadSheetName, summaryHeader, string.Format(Constants.a3, endSummaryTable.ToString()), Constants.summaryTable); }