static void Main(string[] args)
{
ExcelOperator excel = new ExcelOperator(); //创建Excel操作者
excel.CreateExcel(); //创建Excel表格
Console.ReadLine();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM student_info",
"Data Source=.\\SQLEXPRESS;Initial Catalog=student;Integrated Security=True");
DataSet dataSet = new DataSet();
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];
}
//把学生信息表写入到Excel中
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];
if (indexColumn == 4 && range.Value2 < 60) //标记不合格的学生成绩
range.Interior.ColorIndex = 6;
}
}
Range rangeAverage = excel[22, 1];
rangeAverage.Value2 = "平均值";
Range rangeAge = excel[22, 3];
rangeAge.Formula = "=AVERAGE(C2:C21)"; //计算平均年龄
Range rangeGrade = excel[22, 4];
rangeGrade.Formula = "=AVERAGE(D2:D21)"; //计算平均年级
Range rangeResult = excel[22, 5];
rangeResult.Formula = "=AVERAGE(E2:E21)"; //计算数据成绩
excel.SaveExcel(Directory.GetCurrentDirectory() + "\\测试表格.xlsx");//保存Excel表格
Console.ReadLine();
excel.QuitExcel(); //退出Excel应用
}
static void Main(string[] args)
{
Console.Title = "读取Excel表格";
ExcelOperator excel = new ExcelOperator(); //创建Excel操作对象
excel.OpenExcel(Directory.GetCurrentDirectory() + "\\测试表格.xlsx");//打开Excel表格
int indexRow = 1;
int indexColumn = 1;
Range range = excel[indexRow, indexColumn]; //获取Excel中指定单元格
while (range.Value2 != null)
{//遍历行
while (range.Value2 != null)
{//遍历列
Console.Write(range.Value2 + "\t"); //输出单元格中内容
range = excel[indexRow, ++indexColumn];
}
indexColumn = 1;
range = excel[++indexRow, indexColumn];
Console.WriteLine();
}
excel.QuitExcel();
Console.ReadLine();
}