private void btnMergeSheet_ItemClick(object sender, ItemClickEventArgs e)
{
OpenFileDialog o = new OpenFileDialog();
o.Multiselect = true;
o.Filter = "表格文件(*.xlsx)|*.xlsx";
o.Title = "打开需要合并的Excel文件";
List <Workbook> books = new List <Workbook>();
if (o.ShowDialog() == DialogResult.OK)
{
foreach (string s in o.FileNames)
{
Workbook sourcebook = new Workbook(s);
books.Add(sourcebook);
}
SaveFileDialog sFD = new SaveFileDialog();
sFD.Filter = "表格文件(*.xlsx)|*.xlsx";
sFD.InitialDirectory = GetDirectoryInfo(3).FullName;
if (sFD.ShowDialog() == DialogResult.OK)
{
Workbook targetbook = books.First();
books.Remove(targetbook);
foreach (Workbook w in books)
{
targetbook.Combine(w);
targetbook.Save(sFD.FileName);
}
}
}
}
void workCheck_DoWork(object sender, DoWorkEventArgs e)
{
try
{
workCheck.ReportProgress(0, "开始处理...");
string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true);
Workbook template = new Workbook(listAllExcel[0]);
foreach (var item in listAllExcel)
{
if (item == listAllExcel[0])
{
continue;
}
Workbook wb = new Workbook(item);
template.Combine(wb);
}
for (int i = 0; i < template.Worksheets.Count; i++)
{
if (!IsExcuteSheet(template.Worksheets[i].Name))
{
template.Worksheets.RemoveAt(i);
i--;
}
}
template.Save(txtResult.Text);
workCheck.ReportProgress(0, "处理完成");
}
catch (Exception ex)
{
workCheck.ReportProgress(0, ex.Message);
}
}
///<Summary>
/// Merge method to merge excel files
///</Summary>
public Response Merge(string fileName1, string fileName2, string folderName)
{
string outFileExtension = Path.GetExtension(fileName1).ToLower();
License.SetAsposeCellsLicense();
var combinedDocument = string.Format("{0}_CombineTo_{1}" + outFileExtension,
Path.GetFileNameWithoutExtension(fileName1), Path.GetFileNameWithoutExtension(fileName2));
return(Process(this.GetType().Name, combinedDocument, folderName, outFileExtension, false, false, "Merge",
(inFilePath, outPath, zipOutFolder) =>
{
var wb1 = new Workbook(Aspose.App.Live.Demos.UI.Config.Configuration.WorkingDirectory + folderName + "/" + fileName1);
var wb2 = new Workbook(Aspose.App.Live.Demos.UI.Config.Configuration.WorkingDirectory + folderName + "/" + fileName2);
wb1.Combine(wb2);
wb1.Save(outPath);
}));
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Define the first source
//Open the first excel file.
Workbook SourceBook1 = new Workbook(dataDir+ "SampleChart.xlsx");
//Define the second source book.
//Open the second excel file.
Workbook SourceBook2 = new Workbook(dataDir+ "SampleImage.xlsx");
//Combining the two workbooks
SourceBook1.Combine(SourceBook2);
//Save the target book file.
SourceBook1.Save(dataDir+ "Combined.xlsx");
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Path.GetFullPath("../../../Data/");
//Define the first source
//Open the first excel file.
Workbook SourceBook1 = new Workbook(dataDir + "SampleChart.xlsx");
//Define the second source book.
//Open the second excel file.
Workbook SourceBook2 = new Workbook(dataDir + "SampleImage.xlsx");
//Combining the two workbooks
SourceBook1.Combine(SourceBook2);
//Save the target book file.
SourceBook1.Save(dataDir + "Combined.xlsx");
}
public static void Main(string[] args)
{
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Define the first source
//Open the first excel file.
Workbook SourceBook1 = new Workbook(dataDir+ "SampleChart.xlsx");
//Define the second source book.
//Open the second excel file.
Workbook SourceBook2 = new Workbook(dataDir+ "SampleImage.xlsx");
//Combining the two workbooks
SourceBook1.Combine(SourceBook2);
//Save the target book file.
SourceBook1.Save(dataDir+ "Combined.xlsx");
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Define the first source
//Open the first excel file.
Workbook SourceBook1 = new Workbook(dataDir + "SampleChart.xlsx");
//Define the second source book.
//Open the second excel file.
Workbook SourceBook2 = new Workbook(dataDir + "SampleImage.xlsx");
//Combining the two workbooks
SourceBook1.Combine(SourceBook2);
//Save the target book file.
SourceBook1.Save(dataDir + "Combined.out.xlsx");
//ExEnd:1
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Define the first source
// Open the first excel file.
Workbook SourceBook1 = new Workbook(dataDir+ "SampleChart.xlsx");
// Define the second source book.
// Open the second excel file.
Workbook SourceBook2 = new Workbook(dataDir+ "SampleImage.xlsx");
// Combining the two workbooks
SourceBook1.Combine(SourceBook2);
dataDir = dataDir + "Combined.out.xlsx";
// Save the target book file.
SourceBook1.Save(dataDir);
// ExEnd:1
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
public static void Run()
{
// ExStart:1
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Define the first source
// Open the first excel file.
Workbook SourceBook1 = new Workbook(dataDir + "SampleChart.xlsx");
// Define the second source book.
// Open the second excel file.
Workbook SourceBook2 = new Workbook(dataDir + "SampleImage.xlsx");
// Combining the two workbooks
SourceBook1.Combine(SourceBook2);
dataDir = dataDir + "Combined.out.xlsx";
// Save the target book file.
SourceBook1.Save(dataDir);
// ExEnd:1
Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir);
}
public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Define the first source
// Open the first excel file.
Workbook SourceBook1 = new Workbook(sourceDir + "sampleCombineMultipleWorkbooksSingleWorkbook_Chart.xlsx");
// Define the second source book.
// Open the second excel file.
Workbook SourceBook2 = new Workbook(sourceDir + "sampleCombineMultipleWorkbooksSingleWorkbook_Image.xlsx");
// Combining the two workbooks
SourceBook1.Combine(SourceBook2);
// Save the target book file.
SourceBook1.Save(outputDir + "outputCombineMultipleWorkbooksSingleWorkbook.xlsx");
Console.WriteLine("CombineMultipleWorkbooksSingleWorkbook executed successfully.\r\n");
}
private void btnExport_Click(object sender, EventArgs e)
{
if (this.dgvData.SelectedRows.Count == 0)
{
MessageBox.Show("請先選擇列印資料。");
return;
}
this.circularProgress.Visible = true;
this.circularProgress.IsRunning = true;
this.btnQuery.Enabled = false;
this.btnExport.Enabled = false;
this.btnExit.Enabled = false;
string file_name = string.Empty;
int option = 3;
if (this.radioSubject.Checked)
option = 1;
if (this.radioCourse.Checked)
option = 2;
List<UDT.TeacherStatistics> Statistics = new List<UDT.TeacherStatistics>();
this.dgvData.SelectedRows.Cast<DataGridViewRow>().ToList().ForEach(x => Statistics.Add(x.Tag as UDT.TeacherStatistics));
Task<Dictionary<string, Workbook>> task = Task<Dictionary<string, Workbook>>.Factory.StartNew(() =>
{
Dictionary<string, List<UDT.TeacherStatistics>> dicStatistics = new Dictionary<string, List<UDT.TeacherStatistics>>();
Dictionary<string, Workbook> dicFiles = new Dictionary<string, Workbook>();
List<UDT.TeacherStatistics> SelectedStatistics = new List<UDT.TeacherStatistics>();
List<UDT.QHRelation> QHRelations = Access.Select<UDT.QHRelation>();
List<UDT.Hierarchy> Hierarchies = Access.Select<UDT.Hierarchy>();
Dictionary<string, UDT.Hierarchy> dicHierarchies = new Dictionary<string, UDT.Hierarchy>();
if (Hierarchies.Count > 0)
dicHierarchies = Hierarchies.ToDictionary(x => x.Title);
Dictionary<string, UDT.Hierarchy> dicQuestionHierarchies = new Dictionary<string, UDT.Hierarchy>();
foreach (UDT.QHRelation QHRelation in QHRelations)
{
if (dicHierarchies.ContainsKey(QHRelation.HierarchyTitle))
dicQuestionHierarchies.Add(QHRelation.QuestionID.ToString(), dicHierarchies[QHRelation.HierarchyTitle]);
}
//key = SchoolYear + "-" + Semester + "-" + SubjectName;
DataTable dataTable = Query.Select(@"select aSurvey.ref_course_id, aSurvey.ref_teacher_id, survey.name as survey_name, rt.ref_survey_id as survey_id from $ischool.emba.teaching_evaluation.survey as survey
join $ischool.emba.teaching_evaluation.assigned_survey as aSurvey on survey.uid=aSurvey.ref_survey_id
left join $ischool.emba.teaching_evaluation.report_template as rt on aSurvey.ref_survey_id=rt.ref_survey_id");
if (dataTable.Rows.Count == 0)
throw new Exception("請先設定「教學意見表樣版」。");
Dictionary<string, Dictionary<string, string>> dicReportTemplates = new Dictionary<string, Dictionary<string, string>>();
dataTable.Rows.Cast<DataRow>().ToList().ForEach(x =>
{
string key = x["ref_course_id"] + "-" + x["ref_teacher_id"];
string survey_id = x["survey_id"] + "";
string survey_name = x["survey_name"] + "";
if (!dicReportTemplates.ContainsKey(key))
dicReportTemplates.Add(key, new Dictionary<string, string>());
dicReportTemplates[key].Add(survey_id, survey_name);
});
List<string> Empty_Survey = new List<string>();
Statistics.ForEach((x) =>
{
XDocument xDocument = XDocument.Parse(x.StatisticsList, LoadOptions.None);
XElement xStatistics = xDocument.Element("Statistics");
string TeacherName = HttpUtility.HtmlDecode(xStatistics.Attribute("TeacherName").Value); // 授課教師
string CourseName = HttpUtility.HtmlDecode(xStatistics.Attribute("CourseName").Value); // 開課
string SubjectName = HttpUtility.HtmlDecode(xStatistics.Attribute("SubjectName").Value); // 課程
string SchoolYear = xStatistics.Attribute("SchoolYear").Value; // 學年度
string Semester = xStatistics.Attribute("Semester").Value; // 學期
string key = x.CourseID + "-" + x.TeacherID;
if (String.IsNullOrEmpty(dicReportTemplates[key].ElementAt(0).Key))
Empty_Survey.Add(dicReportTemplates[key].ElementAt(0).Value);
if (option == 1)
file_name = SubjectName + "-課程教學評鑑統計表";
if (option == 2)
file_name = CourseName + "-開課教學評鑑統計表";
if (option == 3)
file_name = TeacherName + "-授課教師教學評鑑統計表";
if (!dicStatistics.ContainsKey(file_name))
dicStatistics.Add(file_name, new List<UDT.TeacherStatistics>());
dicStatistics[file_name].Add(x);
});
if (Empty_Survey.Count>0)
{
throw new Exception(string.Format("請先設定下列評鑑之教學意見表樣版：\n\n{0}", string.Join("\n", Empty_Survey.Distinct())));
}
// 統計群組、背景顏色
List<UDT.StatisticsGroup> StatisticsGroups = Access.Select<UDT.StatisticsGroup>();
Dictionary<string, Dictionary<string, Color>> dicQuestionBackgroundColor = new Dictionary<string, Dictionary<string, Color>>();
Dictionary<string, Dictionary<string, Color>> dicEvaluationBackgroundColor = new Dictionary<string, Dictionary<string, Color>>();
StatisticsGroups.ForEach((x) =>
{
XDocument xxDocument = XDocument.Parse(x.DisplayOrderList, LoadOptions.None);
List<XElement> xElements = xxDocument.Descendants("Question").ToList();
if (!string.IsNullOrEmpty(x.QuestionBgColor) && xElements.Count() > 0)
{
foreach (string display_order in xElements.Select(y => y.Attribute("DisplayOrder").Value))
{
if (!dicQuestionBackgroundColor.ContainsKey(x.SurveyID.ToString()))
dicQuestionBackgroundColor.Add(x.SurveyID.ToString(), new Dictionary<string, Color>());
if (!dicQuestionBackgroundColor[x.SurveyID.ToString()].ContainsKey(display_order))
dicQuestionBackgroundColor[x.SurveyID.ToString()].Add(display_order, Color.FromName(x.QuestionBgColor));
else
{
if (Color.FromName(x.QuestionBgColor) != Color.White)
dicQuestionBackgroundColor[x.SurveyID.ToString()][display_order] = Color.FromName(x.QuestionBgColor);
}
}
}
if (!dicEvaluationBackgroundColor.ContainsKey(x.SurveyID.ToString()))
dicEvaluationBackgroundColor.Add(x.SurveyID.ToString(), new Dictionary<string, Color>());
if (!dicEvaluationBackgroundColor[x.SurveyID.ToString()].ContainsKey(x.Name))
dicEvaluationBackgroundColor[x.SurveyID.ToString()].Add(x.Name, Color.FromName(x.EvaluationBgColor));
});
Workbook wb = new Workbook();
foreach (string key in dicStatistics.Keys)
{
Workbook new_workbook = new Workbook();
//new_workbook.Worksheets.Cast<Worksheet>().ToList().ForEach(x => new_workbook.Worksheets.RemoveAt(x.Index));
foreach (UDT.TeacherStatistics Statistic in dicStatistics[key])
{
wb = MakeExcelDocument(Statistic, dicQuestionHierarchies, dicQuestionBackgroundColor, dicEvaluationBackgroundColor);
new_workbook.Combine(wb);
wb.Worksheets.Cast<Worksheet>().ToList().ForEach(x => wb.Worksheets.RemoveAt(x.Index));
}
new_workbook.Worksheets.Cast<Worksheet>().ToList().ForEach((x) =>
{
if (x.Cells.MaxDataColumn == 0 && x.Cells.MaxDataRow == 0)
new_workbook.Worksheets.RemoveAt(x.Index);
});
dicFiles.Add(key, new_workbook);
}
return dicFiles;
});
task.ContinueWith((x) =>
{
this.circularProgress.IsRunning = false;
this.circularProgress.Visible = false;
this.btnQuery.Enabled = true;
this.btnExport.Enabled = true;
this.btnExit.Enabled = true;
if (x.Exception != null)
{
MessageBox.Show(x.Exception.InnerException.Message);
return;
}
string filePath = string.Empty;
System.Windows.Forms.FolderBrowserDialog folder = new FolderBrowserDialog();
do
{
DialogResult dr = folder.ShowDialog();
if (dr == DialogResult.OK)
filePath = folder.SelectedPath;
if (dr == DialogResult.Cancel)
return;
} while (!System.IO.Directory.Exists(filePath));
foreach (string fileName in x.Result.Keys)
{
try
{
// 檔案名稱不能有下列字元<>:"/\|?*
string new_fileName = fileName.Replace("：", "꞉").Replace(":", "꞉").Replace("/", "⁄").Replace("／", "⁄").Replace(@"\", "∖").Replace("＼", "∖").Replace("?", "_").Replace("？", "_").Replace("*", "✻").Replace("＊", "✻").Replace("<", "〈").Replace("＜", "〈").Replace(">", "〉").Replace("＞", "〉").Replace("\"", "''").Replace("”", "''").Replace("|", "ㅣ").Replace("｜", "ㅣ");
x.Result[fileName].Save(Path.Combine(filePath, new_fileName + ".xls"), FileFormatType.Excel2003);
System.Diagnostics.Process.Start(filePath);
}
catch
{
MessageBox.Show("指定路徑無法存取。", "建立檔案失敗", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}, System.Threading.CancellationToken.None, TaskContinuationOptions.None, TaskScheduler.FromCurrentSynchronizationContext());
}
protected void downloadexcelbtn_Click(object sender, EventArgs e)
{
getdata();
ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('downloadexcelbtn_Click');</script>");
//设置EXCEL列宽
int[] ColumnWidth = { 10, 20, 20, 20, 20, 20, 20, 30, 20, 20, 20, 50 };
//获取用户选择的excel文件名称
string ReportTitleName = "ERS_Report";
string savepath = Server.MapPath("Files/" + ReportTitleName.ToString() + ".xls");
//新建excel
Workbook wb = new Workbook();
//设置字体样式
Aspose.Cells.Style style1 = wb.Styles[wb.Styles.Add()];
style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style1.Font.Name = "宋体";
style1.Font.IsBold = true; //设置粗体
style1.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style2 = wb.Styles[wb.Styles.Add()];
style2.HorizontalAlignment = TextAlignmentType.Center;
style2.Font.Size = 10;
//sheet1
Worksheet ws = wb.Worksheets[0];
Cells cell = ws.Cells;
ws.Name = "Telephone";
//合并第一行单元格
Range range = cell.CreateRange(0, 0, 1, ColumnWidth.Length);
range.Merge();
cell["A1"].PutValue("ERS Report"); //标题
//给单元格关联样式
cell["A1"].SetStyle(style1); //报表名字 样式
//设置Execl列名 可以采用单独传值
cell[1, 0].PutValue("Station");
cell[1, 0].SetStyle(style2);
cell[1, 1].PutValue("Number");
cell[1, 1].SetStyle(style2);
cell[1, 2].PutValue("Issue Date");
cell[1, 2].SetStyle(style2);
cell[1, 3].PutValue("Received");
cell[1, 3].SetStyle(style2);
cell[1, 4].PutValue("Balance");
cell[1, 4].SetStyle(style2);
cell[1, 5].PutValue("Sales");
cell[1, 5].SetStyle(style2);
cell[1, 6].PutValue("Deposit");
cell[1, 6].SetStyle(style2);
cell[1, 7].PutValue("RTNG");
cell[1, 7].SetStyle(style2);
cell[1, 8].PutValue("PNR");
cell[1, 8].SetStyle(style2);
cell[1, 9].PutValue("TTL");
cell[1, 9].SetStyle(style2);
cell[1, 10].PutValue("Total Amount");
cell[1, 10].SetStyle(style2);
cell[1, 11].PutValue("Remarks");
cell[1, 11].SetStyle(style2);
//设置单元格内容
int posStart = 2;
int row = 0;
for (int i = 0; i < ds.Tables["Receipt"].Rows.Count; i++)
{
DataRow Drow = ds.Tables["Receipt"].Rows[i];
cell[row + posStart, 0].PutValue(Drow[1].ToString());
cell[row + posStart, 0].SetStyle(style2);
cell[row + posStart, 1].PutValue(string.Format("{0:d6}", Convert.ToInt32(Drow[2])));
cell[row + posStart, 1].SetStyle(style2);
cell[row + posStart, 2].PutValue(Convert.ToDateTime(Drow[6].ToString()).ToShortDateString());
cell[row + posStart, 2].SetStyle(style2);
cell[row + posStart, 3].PutValue(Drow[7].ToString());
cell[row + posStart, 3].SetStyle(style2);
cell[row + posStart, 4].PutValue(Drow[19].ToString());
cell[row + posStart, 4].SetStyle(style2);
cell[row + posStart, 5].PutValue(Drow[4].ToString());
cell[row + posStart, 5].SetStyle(style2);
cell[row + posStart, 6].PutValue(Drow[3].ToString());
cell[row + posStart, 6].SetStyle(style2);
cell[row + posStart, 7].PutValue(Drow[8].ToString());
cell[row + posStart, 7].SetStyle(style2);
cell[row + posStart, 8].PutValue(Drow[9].ToString());
cell[row + posStart, 8].SetStyle(style2);
cell[row + posStart, 9].PutValue(Drow[10].ToString());
cell[row + posStart, 9].SetStyle(style2);
cell[row + posStart, 10].PutValue(Drow[18].ToString());
cell[row + posStart, 10].SetStyle(style2);
cell[row + posStart, 11].PutValue(Drow[11].ToString());
cell[row + posStart, 11].SetStyle(style2);
row++;
}
for (int i = 0; i < ColumnWidth.Length; i++)
{
cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth[i].ToString()));
}
Workbook wb1 = new Workbook();
//设置字体样式
Aspose.Cells.Style style11 = wb1.Styles[wb1.Styles.Add()];
style11.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style11.Font.Name = "宋体";
style11.Font.IsBold = true; //设置粗体
style11.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style21 = wb1.Styles[wb1.Styles.Add()];
style21.HorizontalAlignment = TextAlignmentType.Center;
style21.Font.Size = 10;
//保存在服务器
wb.Combine(wb1);
wb.Save(savepath);
FileTo(ReportTitleName);
}
public void Execute()
{
try
{
DialogResult dialogResult = this._User_Form.ShowDialog();
if (dialogResult != DialogResult.OK)
return;
this._Statistics = this._User_Form.SelectedStatistics;
this._FileType = this._User_Form.FileType;
Task<Dictionary<string, Workbook>> task = Task<Dictionary<string, Workbook>>.Factory.StartNew(() =>
{
List<UDT.QHRelation> QHRelations = Access.Select<UDT.QHRelation>();
List<UDT.Hierarchy> Hierarchies = Access.Select<UDT.Hierarchy>();
Dictionary<string, Workbook> dicSurveyIDs = new Dictionary<string, Workbook>();
Dictionary<string, List<Workbook>> dicWorkbooks = new Dictionary<string, List<Workbook>>();
//Parallel.ForEach<UDT.TeacherStatistics>(this._Statistics, x =>
//{
this._Statistics.ForEach((x) =>
{
try
{
XDocument xDocument = XDocument.Parse(x.StatisticsList, LoadOptions.None);
XElement xStatistics = xDocument.Element("Statistics");
string SurveyID = xStatistics.Attribute("SurveyID").Value;
lock (dicSurveyIDs)
{
if (!dicSurveyIDs.ContainsKey(SurveyID))
dicSurveyIDs.Add(SurveyID, this.GetSurveyTemplate(SurveyID));
}
ExcelDocumentMaker excelDocumentMaker = null;// new ExcelDocumentMaker(QHRelations, Hierarchies, x, dicSurveyIDs[SurveyID]);
Workbook wb = excelDocumentMaker.Produce();
if (wb != null)
{
string key = string.Empty;
if (this._FileType == 1)
key = excelDocumentMaker.SubjectName + "-課程教學評鑑統計表";
if (this._FileType == 2)
key = excelDocumentMaker.CourseName + "-開課教學評鑑統計表";
if (this._FileType == 3)
key = excelDocumentMaker.TeacherName + "-授課教師教學評鑑統計表";
lock (dicWorkbooks)
{
if (!dicWorkbooks.ContainsKey(key))
dicWorkbooks.Add(key, new List<Workbook>());
dicWorkbooks[key].Add(wb);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
});
Dictionary<string, Workbook> dicFiles = new Dictionary<string, Workbook>();
try
{
foreach (string key in dicWorkbooks.Keys)
{
Workbook new_workbook = new Workbook();
foreach (Workbook wb in dicWorkbooks[key])
{
new_workbook.Combine(wb);
wb.Worksheets.Cast<Worksheet>().ToList().ForEach(x => wb.Worksheets.RemoveAt(x.Index));
}
new_workbook.Worksheets.Cast<Worksheet>().ToList().ForEach((x) =>
{
if (x.Cells.MaxDataColumn == 0 && x.Cells.MaxDataRow == 0)
new_workbook.Worksheets.RemoveAt(x.Index);
});
dicFiles.Add(key, new_workbook);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (dicFiles.Count == 0)
throw new Exception("無檔案產生。");
return dicFiles;
});
task.ContinueWith((x) =>
{
if (x.Exception != null)
{
MessageBox.Show(x.Exception.InnerException.Message);
return;
}
string filePath = string.Empty;
System.Windows.Forms.FolderBrowserDialog folder = new FolderBrowserDialog();
do
{
DialogResult dr = folder.ShowDialog();
if (dr == DialogResult.OK)
filePath = folder.SelectedPath;
if (dr == DialogResult.Cancel)
return;
} while (!System.IO.Directory.Exists(filePath));
foreach (string fileName in x.Result.Keys)
{
try
{
// 檔案名稱不能有下列字元<>:"/\|?*
string new_fileName = fileName.Replace("：", "꞉").Replace(":", "꞉").Replace("/", "⁄").Replace("／", "⁄").Replace(@"\", "∖").Replace("＼", "∖").Replace("?", "_").Replace("？", "_").Replace("*", "✻").Replace("＊", "✻").Replace("<", "〈").Replace("＜", "〈").Replace(">", "〉").Replace("＞", "〉").Replace("\"", "''").Replace("”", "''").Replace("|", "ㅣ").Replace("｜", "ㅣ");
x.Result[fileName].Save(Path.Combine(filePath, new_fileName + ".xls"), FileFormatType.Excel2003);
System.Diagnostics.Process.Start(filePath);
}
catch
{
MessageBox.Show("指定路徑無法存取。", "建立檔案失敗", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}, System.Threading.CancellationToken.None, TaskContinuationOptions.None, TaskScheduler.FromCurrentSynchronizationContext());
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
public void Page_Load(object sender, EventArgs e)
{
//获取传递参数
string StaffID = Request.QueryString["StaffID"];
//string SQLP = StaffID.Replace("|", ",");
string SQLP = StaffID.Replace("checkbox", "");
string[] ar = SQLP.Split('|');
for (int i = 0; i < ar.Length; i++)
{
ar[i] = "'" + ar[i] + "'";
}
string SQLS = "";
for (int i = 0; i < ar.Length; i++)
{
if (i == ar.Length - 1)
{
SQLS += ar[i];
}
else
{
SQLS += ar[i] + ",";
}
}
DataTable list = new DataTable();
DataTable list1 = new DataTable();
DataTable list2 = new DataTable();
//DataTable dt3 = new DataTable();
//获取电话数据
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
string SQL = " select A.EMPLID,A.PHONE_TYPE,A.PHONE,A.EXTENSION,A.PREF_PHONE_FLAG from HR_PIF_Phone A WHERE 1=1 AND EMPLID IN(" + SQLS + ") ";
sqlcmm.CommandText = SQL;
sqlcnn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
adapter.Fill(list);
}
}
//获取地址
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm1 = sqlcnn.CreateCommand())
{
string SQL1 = " select * from HR_PIF_Address A WHERE 1=1 AND EMPLID IN(" + SQLS + ") ";
sqlcmm1.CommandText = SQL1;
sqlcnn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm1);
adapter.Fill(list1);
}
}
//获取联系人
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm2 = sqlcnn.CreateCommand())
{
string SQL2 = " select * from HR_PIF_Dependent A WHERE 1=1 AND EMPLID IN(" + SQLS + ") ";
sqlcmm2.CommandText = SQL2;
sqlcnn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm2);
adapter.Fill(list2);
}
}
string sqlstr1 = "Server=192.168.101.114;uid=falcon;pwd=airmacau;database=CSD";//ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection sqlcnn = new SqlConnection(sqlstr1))
{
using (SqlCommand sqlcmmd = sqlcnn.CreateCommand())
{
string SQL = "update HR_PIF_StaffList SET DOWNLOAD_FLAG=1 WHERE EMPLID IN(" + SQLS + ") ";
sqlcmmd.CommandText = SQL;
sqlcnn.Open();
int d = sqlcmmd.ExecuteNonQuery();
}
}
//设置EXCEL列宽
int[] ColumnWidth = { 10, 10, 20, 20, 10 };
int[] ColumnWidth1 = { 10, 10, 20, 10, 50, 50, 50, 50 };
int[] ColumnWidth2 = { 10, 10, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20 };
//int[] list3 = { 10, 10, 20, 20, 10 };
//ReportToExcel(dt, list, "人员登记表", dt1, list1, "人员登记表", dt2, list2, "人员登记表");
//获取用户选择的excel文件名称
string ReportTitleName = "New_Staff_information";
string savepath = Server.MapPath("Template/" + ReportTitleName.ToString() + ".xls");
//新建excel
Workbook wb = new Workbook();
//设置字体样式
Aspose.Cells.Style style1 = wb.Styles[wb.Styles.Add()];
style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style1.Font.Name = "宋体";
style1.Font.IsBold = true; //设置粗体
style1.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style2 = wb.Styles[wb.Styles.Add()];
style2.HorizontalAlignment = TextAlignmentType.Center;
style2.Font.Size = 10;
//sheet1
Worksheet ws = wb.Worksheets[0];
Cells cell = ws.Cells;
ws.Name = "Telephone";
//合并第一行单元格
Range range = cell.CreateRange(0, 0, 1, ColumnWidth.Length);
range.Merge();
cell["A1"].PutValue("Telephone"); //标题
//给单元格关联样式
cell["A1"].SetStyle(style1); //报表名字 样式
//设置Execl列名 可以采用单独传值
cell[1, 0].PutValue("EMPLID");
cell[1, 0].SetStyle(style2);
cell[1, 1].PutValue("PHONE_TYPE");
cell[1, 1].SetStyle(style2);
cell[1, 2].PutValue("PHONE");
cell[1, 2].SetStyle(style2);
cell[1, 3].PutValue("EXTENSION");
cell[1, 3].SetStyle(style2);
cell[1, 4].PutValue("PREF_PHONE_FLAG");
cell[1, 4].SetStyle(style2);
//设置单元格内容
int posStart = 2;
int row = 0;
for (int i = 0; i < list.Rows.Count; i++)
{
DataRow Drow = list.Rows[i];
cell[row + posStart, 0].PutValue(Drow[0].ToString());
cell[row + posStart, 0].SetStyle(style2);
cell[row + posStart, 1].PutValue(Drow[1].ToString());
cell[row + posStart, 1].SetStyle(style2);
cell[row + posStart, 2].PutValue(Drow[2].ToString());
cell[row + posStart, 2].SetStyle(style2);
cell[row + posStart, 3].PutValue(Drow[3].ToString());
cell[row + posStart, 3].SetStyle(style2);
cell[row + posStart, 4].PutValue(Drow[4].ToString());
cell[row + posStart, 4].SetStyle(style2);
row++;
}
for (int i = 0; i < ColumnWidth.Length; i++)
{
cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth[i].ToString()));
}
Workbook wb1 = new Workbook();
//设置字体样式
Aspose.Cells.Style style11 = wb1.Styles[wb1.Styles.Add()];
style11.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style11.Font.Name = "宋体";
style11.Font.IsBold = true; //设置粗体
style11.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style21 = wb1.Styles[wb1.Styles.Add()];
style21.HorizontalAlignment = TextAlignmentType.Center;
style21.Font.Size = 10;
//sheet2
Worksheet ws1 = wb1.Worksheets[0];
Cells cell1 = ws1.Cells;
ws1.Name = "Address";
//合并第一行单元格
Range range1 = cell1.CreateRange(0, 0, 1, ColumnWidth1.Length);
range1.Merge();
cell1["A1"].PutValue("Address"); //标题
//给单元格关联样式
cell1["A1"].SetStyle(style11); //报表名字 样式
//设置Execl列名 可以采用单独传值
cell1[1, 0].PutValue("EMPLID");
cell1[1, 0].SetStyle(style21);
cell1[1, 1].PutValue("KEYPROP_ADDRESS_TYPE");
cell1[1, 1].SetStyle(style21);
cell1[1, 2].PutValue("KEYPROP_EFFDT");
cell1[1, 2].SetStyle(style21);
cell1[1, 3].PutValue("COUNTRY");
cell1[1, 3].SetStyle(style21);
cell1[1, 4].PutValue("ADDRESS1");
cell1[1, 4].SetStyle(style21);
cell1[1, 5].PutValue("ADDRESS2");
cell1[1, 5].SetStyle(style21);
cell1[1, 6].PutValue("ADDRESS3");
cell1[1, 6].SetStyle(style21);
cell1[1, 7].PutValue("ADDRESS4");
cell1[1, 7].SetStyle(style21);
//设置单元格内容
posStart = 2;
row = 0;
for (int i = 0; i < list1.Rows.Count; i++)
{
DataRow Drow = list1.Rows[i];
cell1[row + posStart, 0].PutValue(Drow[0].ToString());
cell1[row + posStart, 0].SetStyle(style21);
cell1[row + posStart, 1].PutValue(Drow[1].ToString());
cell1[row + posStart, 1].SetStyle(style21);
cell1[row + posStart, 2].PutValue(Drow[2].ToString());
cell1[row + posStart, 2].SetStyle(style21);
cell1[row + posStart, 3].PutValue(Drow[3].ToString());
cell1[row + posStart, 3].SetStyle(style21);
cell1[row + posStart, 4].PutValue(Drow[4].ToString());
cell1[row + posStart, 4].SetStyle(style21);
cell1[row + posStart, 5].PutValue(Drow[5].ToString());
cell1[row + posStart, 5].SetStyle(style21);
cell1[row + posStart, 6].PutValue(Drow[6].ToString());
cell1[row + posStart, 6].SetStyle(style21);
cell1[row + posStart, 7].PutValue(Drow[7].ToString());
cell1[row + posStart, 7].SetStyle(style21);
row++;
}
for (int i = 0; i < ColumnWidth1.Length; i++)
{
cell1.SetColumnWidth(i, Convert.ToDouble(ColumnWidth1[i].ToString()));
}
//sheet3
Workbook wb2 = new Workbook();
//设置字体样式
Aspose.Cells.Style style12 = wb2.Styles[wb2.Styles.Add()];
style11.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style11.Font.Name = "宋体";
style11.Font.IsBold = true; //设置粗体
style11.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style22 = wb2.Styles[wb2.Styles.Add()];
style22.HorizontalAlignment = TextAlignmentType.Center;
style22.Font.Size = 10;
Worksheet ws2 = wb2.Worksheets[0];
Cells cell2 = ws2.Cells;
ws2.Name = "Dependent";
//合并第一行单元格
Range range2 = cell2.CreateRange(0, 0, 1, ColumnWidth2.Length);
range2.Merge();
cell2["A1"].PutValue("Dependent"); //标题
//给单元格关联样式
cell2["A1"].SetStyle(style12); //报表名字 样式
//设置Execl列名 可以采用单独传值
cell2[1, 0].PutValue("EMPLID");
cell2[1, 0].SetStyle(style22);
cell2[1, 1].PutValue("DEPENDENT_BENEF");
cell2[1, 1].SetStyle(style22);
cell2[1, 2].PutValue("BIRTHDATE");
cell2[1, 2].SetStyle(style22);
cell2[1, 3].PutValue("BIRTHPLACE");
cell2[1, 3].SetStyle(style22);
cell2[1, 4].PutValue("BIRTHCOUNTRY");
cell2[1, 4].SetStyle(style22);
cell2[1, 5].PutValue("BIRTHSTATE");
cell2[1, 5].SetStyle(style22);
cell2[1, 6].PutValue("PHONE");
cell2[1, 6].SetStyle(style22);
cell2[1, 7].PutValue("EFFDT");
cell2[1, 7].SetStyle(style22);
cell2[1, 8].PutValue("COUNTRY_NM_FORMAT");
cell2[1, 8].SetStyle(style22);
cell2[1, 9].PutValue("LAST_NAME");
cell2[1, 9].SetStyle(style22);
cell2[1, 10].PutValue("FIRST_NAME");
cell2[1, 10].SetStyle(style22);
cell2[1, 11].PutValue("NAME_PREFIX");
cell2[1, 11].SetStyle(style22);
cell2[1, 12].PutValue("EFFDT_1");
cell2[1, 12].SetStyle(style22);
cell2[1, 13].PutValue("SAME_ADDRESS_EMPL");
cell2[1, 13].SetStyle(style22);
cell2[1, 14].PutValue("COUNTRY");
cell2[1, 14].SetStyle(style22);
cell2[1, 15].PutValue("ADDRESS1");
cell2[1, 15].SetStyle(style22);
cell2[1, 16].PutValue("ADDRESS2");
cell2[1, 16].SetStyle(style22);
cell2[1, 17].PutValue("ADDRESS3");
cell2[1, 17].SetStyle(style22);
cell2[1, 18].PutValue("ADDRESS4");
cell2[1, 18].SetStyle(style22);
cell2[1, 19].PutValue("CITY");
cell2[1, 19].SetStyle(style22);
cell2[1, 20].PutValue("EFFDT_3");
cell2[1, 20].SetStyle(style22);
cell2[1, 21].PutValue("RELATIONSHIP");
cell2[1, 21].SetStyle(style22);
cell2[1, 22].PutValue("MAR_STATUS");
cell2[1, 22].SetStyle(style22);
cell2[1, 23].PutValue("MAR_STATUS_DT");
cell2[1, 23].SetStyle(style22);
cell2[1, 24].PutValue("SEX");
cell2[1, 24].SetStyle(style22);
cell2[1, 25].PutValue("GC_DEP_COMMENT");
cell2[1, 25].SetStyle(style22);
cell2[1, 26].PutValue("GC_DEP_EMG_CONTACT");
cell2[1, 26].SetStyle(style22);
cell2[1, 27].PutValue("GC_DEP_PRMCONTACT");
cell2[1, 27].SetStyle(style22);
cell2[1, 28].PutValue("NATIONAL_ID_TYPE");
cell2[1, 28].SetStyle(style22);
cell2[1, 29].PutValue("NATIONAL_ID");
cell2[1, 29].SetStyle(style22);
cell2[1, 30].PutValue("PRIMARY_NID");
cell2[1, 30].SetStyle(style22);
cell2[1, 31].PutValue("EXPIRATION_DATE");
cell2[1, 31].SetStyle(style22);
;
//设置单元格内容
posStart = 2;
row = 0;
for (int i = 0; i < list2.Rows.Count; i++)
{
DataRow Drow = list2.Rows[i];
for (int j = 0; j < 32; j++)
{
cell2[row + posStart, j].PutValue(Drow[j].ToString());
cell2[row + posStart, j].SetStyle(style22);
}
row++;
}
for (int i = 0; i < ColumnWidth2.Length; i++)
{
cell2.SetColumnWidth(i, Convert.ToDouble(ColumnWidth2[i].ToString()));
}
//保存在服务器
wb.Combine(wb1);
wb.Combine(wb2);
wb.Save(savepath);
FileTo(ReportTitleName);
}
//导出按钮点击
private void button3_Click(object sender, EventArgs e)
{
//生成报告
String separator = " ";
switch (sampleModel.samplePointNum)
{
case "3":
separator = ini.IniReadValue("记录设置", "separator3").Replace(" ", " ");
break;
case "4":
separator = ini.IniReadValue("记录设置", "separator4").Replace(" ", " ");;
break;
case "5":
separator = ini.IniReadValue("记录设置", "separator5").Replace(" ", " ");
break;
default:
break;
}
//根据数据数量确定excel 文件数
String dataJson = Util.read2File("data/" + sampleModel.sampleNo + ".json", FileMode.Open, FileAccess.Read);
if (!JsonSplit.IsJson(dataJson))
{
MessageBox.Show("请新建记录！", "提示"); return;
}
sampleModel = Util.JsonToObj <SampleModel>(dataJson);
int excelNum = (int)Math.Ceiling((decimal)sampleModel.data.Count / (decimal)(40 * Int32.Parse(sampleModel.samplePointNum)));
//循环excel 填入数据
String[][] insertData = Util.dataFormat(sampleModel.data, Int32.Parse(sampleModel.samplePointNum));
List <String> averages = new List <string>();
if (insertData == null)
{
MessageBox.Show("无数据！", "提示"); return;
}
Workbook combineWorkbook = new Workbook();
Worksheet sheet;
int mark = 0;
for (int i = 0; i < excelNum; i++)
{
try
{
//打开excel文件
using (FileStream fs = File.OpenRead("model/template.xls"))
{
Workbook workbook = new Workbook(fs);
if (workbook == null)
{
return;
}
sheet = workbook.Worksheets[0];
//填入表头数据
sheet.Cells.Rows[1][0].Value = ini.IniReadValue("记录设置", "recordCode");
sheet.Cells.Rows[1][9].Value = @" " + (i + 1); //编号
sheet.Cells.Rows[2][2].Value = sampleModel.sampleNo; //委托编号
autoFontSize(sampleModel.sampleNo, sheet.Cells.Rows[2][2], 46, 1000);
sheet.Cells.Rows[2][5].Value = sampleModel.device; //试验机号
autoFontSize(sampleModel.device, sheet.Cells.Rows[2][5], 46, 153);
sheet.Cells.Rows[2][9].Value = sampleModel.temperature; //温度
sheet.Cells.Rows[3][2].Value = sampleModel.sampleName; //样品名称
autoFontSize(sampleModel.sampleName, sheet.Cells.Rows[3][2], 46, 1000);
sheet.Cells.Rows[3][7].Value = sampleModel.sampleType; //规格型号
autoFontSize(sampleModel.sampleType, sheet.Cells.Rows[3][7], 46, 1000);
sheet.Cells.Rows[4][2].Value = sampleModel.sampleProject; //检验项目
autoFontSize(sampleModel.sampleProject, sheet.Cells.Rows[4][2], 46, 1000);
sheet.Cells.Rows[4][5].Value = sampleModel.productName; //产品标准
autoFontSize(sampleModel.productName, sheet.Cells.Rows[4][5], 46, 96);
sheet.Cells.Rows[4][8].Value = sampleModel.methodName; //方法标准
autoFontSize(sampleModel.methodName, sheet.Cells.Rows[4][8], 46, 1000);
sheet.Cells.Rows[5][1].Value = "读数(" + sampleModel.sampleHardness + ")";
sheet.Cells.Rows[5][6].Value = "读数(" + sampleModel.sampleHardness + ")";
//填入试验数据
int row = 6;
int col = 1;
int max = insertData.Length > (i + 1) * 40 ? (i + 1) * 40 : insertData.Length;
for (int cur = mark; cur < max; cur++)
{
sheet.Cells.Rows[row][col].Value = String.Join(separator, insertData[cur]);
sheet.Cells.Rows[row][col + 3].Value = (Util.sum(insertData[cur], 3) / 3).ToString("0.0");
averages.Add((Util.sum(insertData[cur], 3) / 3).ToString("0.0"));
if (row == 25)
{
row = 6;
col = 6;
}
else
{
row++;
}
mark++;
}
combineWorkbook.Combine(workbook);
}
}
catch (Exception err)
{
log.Error(err.ToString());
MessageBox.Show("导出失败,详情请查看日志", "提示");
return;
}
}
sampleModel.data.Clear();
insertData = null;
//pdf合并excel文件，删除excel文件
//保存目标PDF文件
if (combineWorkbook != null)
{
try
{
String folderPath = ini.IniReadValue("输出设置", "folderPath") + @"\";
if (!Directory.Exists(folderPath))
{
folderPath = Application.StartupPath + @"\output\";
}
String exportType = ini.IniReadValue("输出设置", "exportType");
switch (exportType)
{
case "pdf":
combineWorkbook.Save(folderPath + sampleModel.sampleNo + ".pdf", Aspose.Cells.SaveFormat.Pdf);
break;
case "xlsx":
combineWorkbook.Save(folderPath + sampleModel.sampleNo + ".xlsx", Aspose.Cells.SaveFormat.Xlsx);
break;
case "csv":
combineWorkbook.Save(folderPath + sampleModel.sampleNo + ".csv", Aspose.Cells.SaveFormat.CSV);
break;
case "html":
combineWorkbook.Save(folderPath + sampleModel.sampleNo + ".html", Aspose.Cells.SaveFormat.Html);
break;
default:
combineWorkbook.Save(folderPath + sampleModel.sampleNo + ".pdf", Aspose.Cells.SaveFormat.Pdf);
break;
}
String averageText = String.Join(";", averages.ToArray());
averageTextBox.Text = averageText;
Util.write2File(folderPath + sampleModel.sampleNo + "-平均值.txt", averageText, FileMode.Create, FileAccess.Write);
MessageBox.Show("导出记录成功", "提示");
sampleModel = new SampleModel();
if (comDataManager != null)
{
comDataManager.clear();
}
countTextBox.Text = "0";
button1.Enabled = true;
button2.Enabled = true;
button3.Enabled = false;
unEnabledInput();
}
catch (Exception err)
{
log.Error(err.ToString());
MessageBox.Show("导出记录失败，详情请查看日志", "提示");
}
}
}
//kill
//public void Kill(Microsoft.Office.Interop.Excel.Application excel)
//{
// excel.Quit();
// IntPtr t = new IntPtr(excel.Hwnd);
// int k = 0;
// GetWindowThreadProcessId(t, out k);
// System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
// p.Kill();
//}
protected void downloadlink(object sender, EventArgs e)
{
GetData1();
//设置EXCEL列宽
int[] ColumnWidth = { 10, 20, 20, 20, 50, 50, 20, 30, 30, 50, 20 };
//获取用户选择的excel文件名称
string ReportTitleName = "Authorization_List";
string savepath = Server.MapPath("Files/" + ReportTitleName.ToString() + ".xls");
//新建excel
Workbook wb = new Workbook();
//设置字体样式
Aspose.Cells.Style style1 = wb.Styles[wb.Styles.Add()];
style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style1.Font.Name = "宋体";
style1.Font.IsBold = true; //设置粗体
style1.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style2 = wb.Styles[wb.Styles.Add()];
style2.HorizontalAlignment = TextAlignmentType.Center;
style2.Font.Size = 10;
//sheet1
Worksheet ws = wb.Worksheets[0];
Cells cell = ws.Cells;
ws.Name = "Telephone";
//合并第一行单元格
Aspose.Cells.Range range = cell.CreateRange(0, 0, 1, ColumnWidth.Length);
range.Merge();
cell["A1"].PutValue("Authorization List"); //标题
//给单元格关联样式
cell["A1"].SetStyle(style1); //报表名字 样式
//设置Execl列名 可以采用单独传值
cell[1, 0].PutValue("Staff No.");
cell[1, 0].SetStyle(style2);
cell[1, 1].PutValue("Staff Name");
cell[1, 1].SetStyle(style2);
cell[1, 2].PutValue("Station");
cell[1, 2].SetStyle(style2);
cell[1, 3].PutValue("Division");
cell[1, 3].SetStyle(style2);
cell[1, 4].PutValue("Subject");
cell[1, 4].SetStyle(style2);
cell[1, 5].PutValue("Rating");
cell[1, 5].SetStyle(style2);
cell[1, 6].PutValue("Level");
cell[1, 6].SetStyle(style2);
cell[1, 7].PutValue("Stamp");
cell[1, 7].SetStyle(style2);
cell[1, 8].PutValue("ExpireDate");
cell[1, 8].SetStyle(style2);
cell[1, 9].PutValue("Remarks");
cell[1, 9].SetStyle(style2);
cell[1, 10].PutValue("Valid");
cell[1, 10].SetStyle(style2);
//设置单元格内容
int posStart = 2;
int row = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow Drow = dt.Rows[i];
cell[row + posStart, 0].PutValue(Drow[1].ToString());
cell[row + posStart, 0].SetStyle(style2);
cell[row + posStart, 1].PutValue(Drow[3].ToString());
cell[row + posStart, 1].SetStyle(style2);
cell[row + posStart, 2].PutValue(Drow[14].ToString());
cell[row + posStart, 2].SetStyle(style2);
cell[row + posStart, 3].PutValue(Drow[13].ToString());
cell[row + posStart, 3].SetStyle(style2);
cell[row + posStart, 4].PutValue(Drow[2].ToString());
cell[row + posStart, 4].SetStyle(style2);
cell[row + posStart, 5].PutValue(Drow[4].ToString());
cell[row + posStart, 5].SetStyle(style2);
cell[row + posStart, 6].PutValue(Drow[5].ToString());
cell[row + posStart, 6].SetStyle(style2);
cell[row + posStart, 7].PutValue(Drow[9].ToString());
cell[row + posStart, 7].SetStyle(style2);
cell[row + posStart, 8].PutValue(Convert.ToDateTime(Drow[6].ToString()).ToString("dd-MMM-yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo));
cell[row + posStart, 8].SetStyle(style2);
cell[row + posStart, 9].PutValue(Drow[8].ToString());
cell[row + posStart, 9].SetStyle(style2);
cell[row + posStart, 10].PutValue(Drow[7].ToString());
cell[row + posStart, 10].SetStyle(style2);
row++;
}
for (int i = 0; i < ColumnWidth.Length; i++)
{
cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth[i].ToString()));
}
Workbook wb1 = new Workbook();
//设置字体样式
Aspose.Cells.Style style11 = wb1.Styles[wb1.Styles.Add()];
style11.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style11.Font.Name = "宋体";
style11.Font.IsBold = true; //设置粗体
style11.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style21 = wb1.Styles[wb1.Styles.Add()];
style21.HorizontalAlignment = TextAlignmentType.Center;
style21.Font.Size = 10;
//保存在服务器
wb.Combine(wb1);
wb.Save(savepath);
FileTo(ReportTitleName);
}
private void btnSubjectSemesterScoreHistory_Click(object sender, EventArgs e)
{
this.circularProgress.Visible = true;
this.circularProgress.IsRunning = true;
this.btnPrint.Enabled = false;
int SchoolYear = int.Parse(this.nudSchoolYear.Value + "");
int Semester = int.Parse((this.cboSemester.SelectedItem as EMBACore.DataItems.SemesterItem).Value);
Task<Workbook> task = Task<Workbook>.Factory.StartNew(() =>
{
Workbook new_workbook = new Workbook();
foreach (string key in this.dicStudents.Keys)
{
Workbook wb = this.GenerateWorkbook(this.dicStudents[key], SchoolYear, Semester);
new_workbook.Combine(wb);
new_workbook.Worksheets.Cast<Worksheet>().ToList().ForEach((x) =>
{
if (x.Cells.MaxDataColumn == 0 && x.Cells.MaxDataRow == 0)
new_workbook.Worksheets.RemoveAt(x.Index);
});
}
return new_workbook;
});
task.ContinueWith((x) =>
{
this.circularProgress.Visible = false;
this.circularProgress.IsRunning = false;
this.btnPrint.Enabled = true;
if (x.Exception != null)
{
MessageBox.Show(x.Exception.InnerException.Message);
return;
}
System.Windows.Forms.SaveFileDialog sd = new System.Windows.Forms.SaveFileDialog();
sd.Title = "另存新檔";
sd.FileName = "歷年成績表" + DateTime.Now.ToString(" yyyy-MM-dd_HH_mm_ss") + ".xls";
sd.Filter = "Excel檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*";
sd.AddExtension = true;
if (sd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
x.Result.Save(sd.FileName, FileFormatType.Excel2003);
System.Diagnostics.Process.Start(sd.FileName);
}
catch
{
FISCA.Presentation.Controls.MsgBox.Show("指定路徑無法存取。", "建立檔案失敗", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
return;
}
}
}, System.Threading.CancellationToken.None, TaskContinuationOptions.None, TaskScheduler.FromCurrentSynchronizationContext());
}
public async Task <Response> Merge(string outputType, string mergerType)
{
var sessionId = Guid.NewGuid().ToString();
var action = $"Merge to {outputType}";
try
{
var taskUpload = Task.Run(() => UploadWorkbooks(sessionId));
taskUpload.Wait(Api.Configuration.MillisecondsTimeout);
if (!taskUpload.IsCompleted)
{
NLogger.LogError($"Merge UploadWorkbooks=>{sessionId}=>{AppSettings.ProcessingTimedout}");
throw new TimeoutException(AppSettings.ProcessingTimedout);
}
var docs = taskUpload.Result;
if (docs == null)
{
return(PasswordProtectedResponse);
}
if (docs.Length <= 1 || docs.Length > MaximumUploadFiles)
{
return(MaximumFileLimitsResponse);
}
SetDefaultOptions(docs);
Opts.AppName = "Merger";
Opts.MethodName = "Merge";
Opts.ResultFileName = $"Merged file{Opts.OutputType}";
Opts.CreateZip = Opts.OutputType.Equals(".html");
Opts.ZipFileName = "Merged file";
Opts.FolderName = docs[0].FolderName;
Opts.DeleteSourceFolder = true;
var saveOpt = GetSaveOptions(outputType.Trim().ToLower());
return(await Process((inFilePath, outPath, zipOutFolder) =>
{
var outWorkbook = new Workbook();
if (!IsImage(docs[0].FileName))
{
outWorkbook.Worksheets.RemoveAt(0);
}
var index = 0;
foreach (var doc in docs)
{
switch (Path.GetExtension(doc.FileName))
{
case ".jpg":
case ".png":
var upperLeftRow = outWorkbook.Worksheets[0].Pictures.Count > 0
? outWorkbook.Worksheets[0].Pictures[index].LowerRightRow
: index;
index = outWorkbook.Worksheets[0].Pictures.Add(upperLeftRow, 0, doc.FileName);
break;
case ".html":
case ".mht":
case ".mhtml":
case ".xlsx":
case ".xls":
case ".xlsm":
case ".xlsb":
case ".ods":
case ".csv":
case ".tsv":
outWorkbook.Combine(new Workbook(doc.FileName));
break;
}
}
if (mergerType.Equals("s"))
{
outWorkbook = MergeToSheet(outWorkbook);
}
var documentInfo = new DocumentInfo {
FileName = outPath, FolderName = Path.GetDirectoryName(outPath), Workbook = outWorkbook
};
var stopWatch = new Stopwatch();
stopWatch.Start();
NLogger.LogInfo($"Merge to {outputType.Trim().ToLower()}=>{string.Join(",", docs.Select(t => t.FileName))}=>Start");
var task = Task.Run(() => { SaveDocument(documentInfo, outPath, zipOutFolder, saveOpt); });
var isCompleted = task.Wait(Api.Configuration.MillisecondsTimeout);
if (!isCompleted)
{
NLogger.LogError($"Merge to {outputType.Trim().ToLower()}=>{string.Join(",", docs.Select(t => t.FileName))}=>{AppSettings.ProcessingTimedout}");
throw new TimeoutException(AppSettings.ProcessingTimedout);
}
stopWatch.Stop();
NLogger.LogInfo($"Merge to {outputType.Trim().ToLower()}=>{string.Join(",", docs.Select(t => t.FileName))}=>cost seconds:{stopWatch.Elapsed.TotalSeconds}");
}));
}
catch (Exception e)
{
var exception = e.InnerException ?? e;
var message = $"{exception.Message} | outputType = {outputType} | mergerType = {mergerType}";
NLogger.LogError(App, "Merge", message, sessionId);
return(new Response
{
StatusCode = 500,
Status = exception.Message,
FolderName = sessionId,
Text = action
});
}
}
public void downloadexcel(DataTable dt, string[] title, int[] ColumnWidth, string ReportTitleName)
{
GetData();
string savepath = Server.MapPath("Files/" + ReportTitleName.ToString() + ".xls");
//新建excel
Workbook wb = new Workbook();
//设置字体样式
Aspose.Cells.Style style1 = wb.Styles[wb.Styles.Add()];
style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style1.Font.Name = "宋体";
style1.Font.IsBold = true; //设置粗体
style1.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style2 = wb.Styles[wb.Styles.Add()];
style2.HorizontalAlignment = TextAlignmentType.Center;
style2.Font.Size = 10;
//表头格式
Aspose.Cells.Style style3 = wb.Styles[wb.Styles.Add()];
style3.HorizontalAlignment = TextAlignmentType.Center;
style3.Font.IsBold = true; //设置粗体
style3.Font.Size = 10; //设置字体大小
//sheet1
Worksheet ws = wb.Worksheets[0];
Cells cell = ws.Cells;
ws.Name = "Telephone";
//合并第一行单元格
Aspose.Cells.Range range = cell.CreateRange(0, 0, 1, ColumnWidth.Length);
range.Merge();
cell["A1"].PutValue(ReportTitleName); //标题
//给单元格关联样式
cell["A1"].SetStyle(style1); //报表名字 样式
//设置Execl列名 可以采用单独传值
for (int i = 0; i < title.Length; i++)
{
cell[1, i].PutValue(title[i]);
cell[1, i].SetStyle(style3);
}
//设置单元格内容
int posStart = 2;
int row = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow Drow = dt.Rows[i];
cell[row + posStart, 0].PutValue(Drow["StaffID"].ToString());
cell[row + posStart, 0].SetStyle(style2);
cell[row + posStart, 1].PutValue(Drow["StaffName"].ToString());
cell[row + posStart, 1].SetStyle(style2);
cell[row + posStart, 2].PutValue(Drow["Station"].ToString());
cell[row + posStart, 2].SetStyle(style2);
cell[row + posStart, 3].PutValue(Drow["Division"].ToString());
cell[row + posStart, 3].SetStyle(style2);
cell[row + posStart, 4].PutValue(Drow["Class_Name"].ToString());
cell[row + posStart, 4].SetStyle(style2);
cell[row + posStart, 5].PutValue(Drow["Batch"].ToString());
cell[row + posStart, 5].SetStyle(style2);
cell[row + posStart, 6].PutValue(Drow["Course"].ToString());
cell[row + posStart, 6].SetStyle(style2);
cell[row + posStart, 7].PutValue(Drow["Course_Ref"].ToString());
cell[row + posStart, 7].SetStyle(style2);
cell[row + posStart, 8].PutValue(Convert.ToDateTime(Drow["Training_Date"]).ToString("yyyy-MM-dd"));
cell[row + posStart, 8].SetStyle(style2);
cell[row + posStart, 9].PutValue(Convert.ToDateTime(Drow["Training_Required_Date"]).ToString("yyyy-MM-dd"));
cell[row + posStart, 9].SetStyle(style2);
row++;
}
for (int i = 0; i < ColumnWidth.Length; i++)
{
cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth[i].ToString()));
}
Workbook wb1 = new Workbook();
//设置字体样式
Aspose.Cells.Style style11 = wb1.Styles[wb1.Styles.Add()];
style11.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style11.Font.Name = "宋体";
style11.Font.IsBold = true; //设置粗体
style11.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style21 = wb1.Styles[wb1.Styles.Add()];
style21.HorizontalAlignment = TextAlignmentType.Center;
style21.Font.Size = 10;
//保存在服务器
wb.Combine(wb1);
wb.Save(savepath);
FileTo(ReportTitleName);
}
protected void Download_Click(object sender, EventArgs e)
{
getdata();
//生成EXCEL
//获取用户选择的excel文件名称
string ReportTitleName = "Cabin_Crew_Load_Factor";
string savepath = Server.MapPath("Files/" + ReportTitleName.ToString() + ".xls");
//sheet1 Statistical
//新建excel
Workbook wb = new Workbook();
//设置字体样式
Aspose.Cells.Style style1 = wb.Styles[wb.Styles.Add()];
style1.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style1.Font.Name = "宋体";
style1.Font.IsBold = true; //设置粗体
style1.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style2 = wb.Styles[wb.Styles.Add()];
style2.HorizontalAlignment = TextAlignmentType.Center;
style2.Font.Size = 10;
//设置EXCEL列宽
int[] ColumnWidth1 = { 20, 20, 20 };
Worksheet ws = wb.Worksheets[0];
Cells cell = ws.Cells;
ws.Name = "Statistical";
//合并第一行单元格
Range range1 = cell.CreateRange(0, 0, 1, ColumnWidth1.Length);
range1.Merge();
cell["A1"].PutValue("CCLF Report"); //标题
//给单元格关联样式
cell["A1"].SetStyle(style1); //报表名字 样式
//设置Execl列名 可以采用单独传值
cell[1, 0].PutValue("Staff Id");
cell[1, 0].SetStyle(style2);
cell[1, 1].PutValue("Name");
cell[1, 1].SetStyle(style2);
cell[1, 2].PutValue("Count");
cell[1, 2].SetStyle(style2);
//设置单元格内容
int posStart = 2;
int row = 0;
for (int i = 0; i < ds.Tables["Statistical"].Rows.Count; i++)
{
DataRow Drow = ds.Tables["Statistical"].Rows[i];
cell[row + posStart, 0].PutValue(Drow[0].ToString());
cell[row + posStart, 0].SetStyle(style2);
cell[row + posStart, 1].PutValue(Drow[1].ToString());
cell[row + posStart, 1].SetStyle(style2);
cell[row + posStart, 2].PutValue(Drow[2].ToString());
cell[row + posStart, 2].SetStyle(style2);
row++;
}
for (int i = 0; i < ColumnWidth1.Length; i++)
{
cell.SetColumnWidth(i, Convert.ToDouble(ColumnWidth1[i].ToString()));
}
//sheet2 Detail
Workbook wb1 = new Workbook();
//设置字体样式
int[] ColumnWidth2 = { 30, 20, 20, 20, 20, 20, 20, 30, 20, 20, 20, 20, 50, 20 };
Aspose.Cells.Style style11 = wb1.Styles[wb1.Styles.Add()];
style11.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style11.Font.Name = "宋体";
style11.Font.IsBold = true; //设置粗体
style11.Font.Size = 12; //设置字体大小
Aspose.Cells.Style style21 = wb.Styles[wb.Styles.Add()];
style21.HorizontalAlignment = TextAlignmentType.Center;
style21.Font.Size = 10;
Worksheet ws1 = wb1.Worksheets[0];
Cells cell1 = ws1.Cells;
ws1.Name = "Detail";
Range range2 = cell1.CreateRange(0, 0, 1, ColumnWidth2.Length);
range2.Merge();
cell1["A1"].PutValue("CCLF Report"); //标题
//给单元格关联样式
cell1["A1"].SetStyle(style11); //报表名字 样式
//设置Execl列名 可以采用单独传值
cell1[1, 0].PutValue("FLT_DATE");
cell1[1, 0].SetStyle(style21);
cell1[1, 1].PutValue("IATA_C");
cell1[1, 1].SetStyle(style21);
cell1[1, 2].PutValue("FLT_ID");
cell1[1, 2].SetStyle(style21);
cell1[1, 3].PutValue("AC_ID");
cell1[1, 3].SetStyle(style21);
cell1[1, 4].PutValue("AC_TYPE");
cell1[1, 4].SetStyle(style21);
cell1[1, 5].PutValue("DEP_APT");
cell1[1, 5].SetStyle(style21);
cell1[1, 6].PutValue("ARR_APT");
cell1[1, 6].SetStyle(style21);
cell1[1, 7].PutValue("CANCEL_FLAG");
cell1[1, 7].SetStyle(style21);
cell1[1, 8].PutValue("SEAT");
cell1[1, 8].SetStyle(style21);
cell1[1, 9].PutValue("PAX");
cell1[1, 9].SetStyle(style21);
cell1[1, 10].PutValue("LF");
cell1[1, 10].SetStyle(style21);
cell1[1, 11].PutValue("CABIN_STAFFNO");
cell1[1, 11].SetStyle(style21);
cell1[1, 12].PutValue("CABIN_NAME");
cell1[1, 12].SetStyle(style21);
cell1[1, 13].PutValue("CREW_NUMBER");
cell1[1, 13].SetStyle(style21);
posStart = 2;
row = 0;
for (int i = 0; i < ds.Tables["Detail"].Rows.Count; i++)
{
DataRow Drow = ds.Tables["Detail"].Rows[i];
cell1[row + posStart, 0].PutValue(Convert.ToDateTime(Drow[0].ToString()).ToString("dd-MM-yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo));
cell1[row + posStart, 0].SetStyle(style21);
cell1[row + posStart, 1].PutValue(Drow[1].ToString());
cell1[row + posStart, 1].SetStyle(style21);
cell1[row + posStart, 2].PutValue(Drow[2].ToString());
cell1[row + posStart, 2].SetStyle(style21);
cell1[row + posStart, 3].PutValue(Drow[3].ToString());
cell1[row + posStart, 3].SetStyle(style21);
cell1[row + posStart, 4].PutValue(Drow[4].ToString());
cell1[row + posStart, 4].SetStyle(style21);
cell1[row + posStart, 5].PutValue(Drow[5].ToString());
cell1[row + posStart, 5].SetStyle(style21);
cell1[row + posStart, 6].PutValue(Drow[6].ToString());
cell1[row + posStart, 6].SetStyle(style21);
cell1[row + posStart, 7].PutValue(Drow[7].ToString());
cell1[row + posStart, 7].SetStyle(style21);
cell1[row + posStart, 8].PutValue(Drow[8].ToString());
cell1[row + posStart, 8].SetStyle(style21);
cell1[row + posStart, 9].PutValue(Drow[9].ToString());
cell1[row + posStart, 9].SetStyle(style21);
cell1[row + posStart, 10].PutValue(Drow[10].ToString());
cell1[row + posStart, 10].SetStyle(style21);
cell1[row + posStart, 11].PutValue(Drow[11].ToString());
cell1[row + posStart, 11].SetStyle(style21);
cell1[row + posStart, 12].PutValue(Drow[12].ToString());
cell1[row + posStart, 12].SetStyle(style21);
cell1[row + posStart, 13].PutValue(Drow[13].ToString());
cell1[row + posStart, 13].SetStyle(style21);
row++;
}
for (int i = 0; i < ColumnWidth2.Length; i++)
{
cell1.SetColumnWidth(i, Convert.ToDouble(ColumnWidth2[i].ToString()));
}
//保存在服务器
wb.Combine(wb1);
wb.Save(savepath);
FileTo(ReportTitleName);
}
