private void TrySaveExcel(string name) { ExcelOperator excel = new ExcelOperator(); excel.CreateExcel(); int idxRow = 1, idxColumn = 1; for (idxColumn = 1; idxColumn <= 12; idxColumn++ ) { Range cell = excel[idxRow, idxColumn]; cell.Value2 = __excel__header__[idxColumn - 1]; } idxRow++; foreach (var item in BookList.Data) { for (idxColumn = 1; idxColumn <= 6; idxColumn++ ) { Range cell = excel[idxRow, idxColumn]; cell.Value2 = item.BookInfo._rawData_[idxColumn - 1]; } for (idxColumn = 7; idxColumn <= 12; idxColumn++) { Range cell = excel[idxRow, idxColumn]; cell.Value2 = item.BookPrint._rawData_[idxColumn - 7]; } idxRow++; } excel.SaveExcel(name); excel.QuitExcel(); }
private void TrySaveExcel(string name) { var users = __uid__ucl__.ToList(); int usercnt = users.Count; ExcelOperator excel = new ExcelOperator(); excel.ExcelWorkbook = excel.ExcelApplication.Workbooks.Add(true); excel.CreateExcel(); // ExcelWorksheet Other: Sort by User int idxRow = 1; bool first = true; foreach (var user in users) { int uid = user.Key; string ucl = user.Value; if (first) first = false; else excel.ExcelWorksheet = excel.ExcelWorkbook.Worksheets.Add(); excel.ExcelWorksheet.Name = ucl; idxRow = 1; for (int idxColumn = 1; idxColumn <= 6; idxColumn++) { excel[idxRow, idxColumn].Value2 = __excel__header__[idxColumn - 1]; } excel[idxRow, 7].Value2 = __excel__header__[7]; idxRow = 2; var data = from item in FinalList where item.MainValue.UserID == uid select item; foreach (var item in data) { excel[idxRow, 1].Value2 = item.MainInfo.BookID.ToString(); excel[idxRow, 2].Value2 = item.MainInfo.BookInfo.Name; excel[idxRow, 3].Value2 = __Rank_Word_1__[(int)item.MainValue.Value.Level]; excel[idxRow, 4].Value2 = __Rank_Word_2__[(int)item.MainValue.Value.Application]; excel[idxRow, 5].Value2 = __Rank_Word_3__[(int)item.MainValue.Value.Influence]; excel[idxRow, 6].Value2 = __Rank_Word_4__[(int)item.MainValue.Value.ServiceQuality]; excel[idxRow++, 7].Value2 = item.FinalValue.ToString("F2"); } Range cell = excel.ExcelWorksheet.get_Range("G2", "G" + (data.Count() + 1).ToString()); cell.NumberFormatLocal = "$#,##0.000"; } // ExcelWorksheet 2: All Data excel.ExcelWorksheet = excel.ExcelWorkbook.Worksheets.Add(); excel.ExcelWorksheet.Name = "评价列表"; idxRow = 1; for (int idxColumn = 1; idxColumn <= 8; idxColumn++) { excel[idxRow, idxColumn].Value2 = __excel__header__[idxColumn - 1]; } idxRow++; foreach (var item in FinalList) { excel[idxRow, 1].Value2 = item.MainInfo.BookID.ToString(); excel[idxRow, 2].Value2 = item.MainInfo.BookInfo.Name; excel[idxRow, 3].Value2 = __Rank_Word_1__[(int)item.MainValue.Value.Level]; excel[idxRow, 4].Value2 = __Rank_Word_2__[(int)item.MainValue.Value.Application]; excel[idxRow, 5].Value2 = __Rank_Word_3__[(int)item.MainValue.Value.Influence]; excel[idxRow, 6].Value2 = __Rank_Word_4__[(int)item.MainValue.Value.ServiceQuality]; excel[idxRow, 7].Value2 = __uid__ucl__[item.MainValue.UserID]; excel[idxRow++, 8].Value2 = item.FinalValue.ToString("F2"); } Range cells = excel.ExcelWorksheet.get_Range("H2", "H" + (FinalList.Count() + 1).ToString()); cells.NumberFormatLocal = "$#,##0.000"; // ExcelWorksheet 1: Main Result excel.ExcelWorksheet = excel.ExcelWorkbook.Worksheets.Add(); excel.ExcelWorksheet.Name = "评价结果"; idxRow = 1; excel[idxRow, 1].Value2 = __excel__header__[0]; excel[idxRow, 2].Value2 = __excel__header__[1]; excel[idxRow, 3].Value2 = "平均资助金额"; for (int offset = 1; offset <= usercnt; offset++) { excel[idxRow, 3 + offset].Value2 = users[offset - 1].Value; } idxRow = 2; string flag = ExcelOperator.GetExcelColumnName(3 + usercnt); foreach (var book in BookList.Data) { string rowname = idxRow.ToString(); excel[idxRow, 1].Value2 = book.BookID.ToString(); excel[idxRow, 2].Value2 = book.BookInfo.Name; excel[idxRow, 3].Formula = "=AVERAGE(D" + rowname + ":" + flag + rowname + ")"; for (int offset = 1; offset <= usercnt; offset++) { var result = from item in FinalList where item.MainValue.BookID == book.BookID where item.MainValue.UserID == users[offset - 1].Key select item.FinalValue; if (result.Count() != 0) { excel[idxRow, 3 + offset].Value2 = result.First().ToString("F2"); } } idxRow++; } idxRow--; Range range = excel.ExcelWorksheet.get_Range("C2", flag + idxRow.ToString()); range.NumberFormatLocal = "$#,##0.000"; excel.SaveExcel(name); excel.QuitExcel(); }
private void TryOpenExcel(string name) { ExcelOperator excel = new ExcelOperator(); excel.OpenExcel(name); int idxRow = 2; while (true) { Range test = excel[idxRow, 1]; if (test.Value2 == null) break; else idxRow++; } int cnt = idxRow; Form_ImportProgress fip = new Form_ImportProgress(cnt - 2); fip.Show(); for (idxRow = 2; idxRow < cnt; idxRow++ ) { Range range = excel[idxRow, 1]; string[] raw1 = new string[6]; for (int idxColumn = 1; idxColumn <= 6; idxColumn++) { Range cell = excel[idxRow, idxColumn]; raw1[idxColumn - 1] = cell.Value2; } if (BookList.isExist(raw1[0], raw1[1], raw1[3])) continue; string[] raw2 = new string[6]; int word = (int)Math.Round(excel[idxRow, 7].Value2); raw2[0] = word.ToString(); for (int idxColumn = 8; idxColumn <= 12; idxColumn++) { Range cell = excel[idxRow, idxColumn]; raw2[idxColumn - 7] = (string)cell.Value2; } BookDetail item = new BookDetail(0, raw1, raw2); BookList.Add(BookList.getNextBID(), item); // ProgressBar fip.ChangeTo(idxRow - 1); } excel.QuitExcel(); fip.Close(); }