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();
}
protected void Button6_Click(object sender, EventArgs e)
{
using (SqlConnection cn = new SqlConnection())
{
ExcelOperator excel = new ExcelOperator();
excel.CreateExcel();//创建excel表
cn.ConnectionString = sqlconn;
cn.Open();
SqlCommand cmd = new SqlCommand("select * from 部门", cn); //创建查询类实例
SqlDataAdapter adapter = new SqlDataAdapter(); //实例化数据适配器
adapter.SelectCommand = cmd; //让适配器执行SELECT命令
DataSet dataSet = new DataSet(); //实例化结果数据集
int n = adapter.Fill(dataSet); //将结果放入数据适配器，返回元祖个数
string[] fields = new string[] { "部门代号", "部门名称", "部门主管" };
for (int indexColumn = 0; indexColumn < dataSet.Tables[0].Columns.Count; indexColumn++)
{
Range range = excel[1, indexColumn + 1];
range.Value2 = fields[indexColumn];
}
for (int indexRow = 0; indexRow < dataSet.Tables[0].Rows.Count; indexRow++)
{
for (int indexColumn = 0; indexColumn < dataSet.Tables[0].Columns.Count; indexColumn++)
{
Range range = excel[indexRow + 2, indexColumn + 1];
range.Value2 = dataSet.Tables[0].Rows[indexRow][indexColumn];
}
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
using (SqlConnection cn = new SqlConnection())
{
ExcelOperator excel = new ExcelOperator();
excel.CreateExcel();//创建excel表
cn.ConnectionString = sqlconn;
cn.Open();
string eid = Session["eid"].ToString();
string cmdtext = string.Format("select * from 员工 where eid ='{0}'", eid);//将编辑好的语句连接好
SqlDataAdapter sda = new SqlDataAdapter(cmdtext, cn);
SqlCommand cmd = new SqlCommand(cmdtext, cn);
SqlDataAdapter adapter = new SqlDataAdapter(); //实例化数据适配器
adapter.SelectCommand = cmd; //让适配器执行SELECT命令
DataSet dataSet = new DataSet(); //实例化结果数据集
int n = adapter.Fill(dataSet); //将结果放入数据适配器，返回元祖个数
string[] fields = new string[] { "员工代号", "员工姓名", "所属部门", "年龄", "登入密码", "是否管理员" };
for (int indexColumn = 0; indexColumn < dataSet.Tables[0].Columns.Count; indexColumn++)
{
Range range = excel[1, indexColumn + 1];
range.Value2 = fields[indexColumn];
}
for (int indexRow = 0; indexRow < dataSet.Tables[0].Rows.Count; indexRow++)
{
for (int indexColumn = 0; indexColumn < dataSet.Tables[0].Columns.Count; indexColumn++)
{
Range range = excel[indexRow + 2, indexColumn + 1];
range.Value2 = dataSet.Tables[0].Rows[indexRow][indexColumn];
}
}
}
}
protected void Button4_Click(object sender, EventArgs e)
{
using (SqlConnection cn = new SqlConnection())
{
ExcelOperator excel = new ExcelOperator();
excel.CreateExcel();//创建excel表
cn.ConnectionString = sqlconn;
cn.Open();
DataSet ds = new DataSet();
int first = 0; //用来检测第一个勾选的对象
String selectsql = ""; //用来编写对应的sql查询语句
if (CheckBox_id.Checked) //因为是第一次检测所以不用进行首个勾选的判断
{
first = 1;
selectsql = selectsql + "departID= '" + TextBox_id.Text + "'";
}
if (CheckBox_name.Checked)
{
if (first == 0)//修改检测标记和对应语句
{
first = 1;
selectsql = selectsql + "dname= N'" + TextBox_name.Text + "'";
}
else
{
selectsql = selectsql + "and dname= N'" + TextBox_name.Text + "'";
}
}
if (CheckBox_de.Checked)
{
if (first == 0)//修改检测标记和对应语句
{
first = 1;
if (TextBox_de.Text.Equals(""))
{
selectsql = selectsql + "director is null";
}
else
{
selectsql = selectsql + "director= '" + TextBox_de.Text + "'";
}
}
else
{
if (TextBox_de.Text.Equals(""))
{
selectsql = selectsql + " and director is null";
}
else
{
selectsql = selectsql + "and director= '" + TextBox_de.Text + "'";
}
}
}
if (first == 0)//顺便利用这个标记检测是否至少勾选一个选项，进行报错
{
Label_err.Text = "请至少勾选一个选项进行搜索";
}
string cmdtext = string.Format("select * from 部门 where " + selectsql); //将编辑好的语句连接好
SqlDataAdapter sda = new SqlDataAdapter(cmdtext, cn);
sda.Fill(ds, "部门");
SqlCommand cmd = new SqlCommand(cmdtext, cn);
SqlDataAdapter adapter = new SqlDataAdapter(); //实例化数据适配器
adapter.SelectCommand = cmd; //让适配器执行SELECT命令
DataSet dataSet = new DataSet(); //实例化结果数据集
int n = adapter.Fill(dataSet); //将结果放入数据适配器，返回元祖个数
string[] fields = new string[] { "部门代号", "部门名称", "部门主管" };
for (int indexColumn = 0; indexColumn < dataSet.Tables[0].Columns.Count; indexColumn++)
{
Range range = excel[1, indexColumn + 1];
range.Value2 = fields[indexColumn];
}
for (int indexRow = 0; indexRow < dataSet.Tables[0].Rows.Count; indexRow++)
{
for (int indexColumn = 0; indexColumn < dataSet.Tables[0].Columns.Count; indexColumn++)
{
Range range = excel[indexRow + 2, indexColumn + 1];
range.Value2 = dataSet.Tables[0].Rows[indexRow][indexColumn];
}
}
}
}
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();
}