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