Ejemplo n.º 1
0
 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();
 }
Ejemplo n.º 2
0
        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();
        }
Ejemplo n.º 3
0
 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();
 }