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