C# Excel操作示例

DataTable table = new DataTable();

using (SqlConnection conn = new SqlConnection(@"Server=./SQLExpress;DataBase=CHSJ_FaceMatch;UID=sa;PWD=sa"))

{

    conn.Open();

    using (SqlCommand cmd = conn.CreateCommand())

    {

        cmd.CommandText = "select top 10 IdentityID,Name,Birthday,NativePlace,TrainDataFile from T_Criminal";

        table.Load(cmd.ExecuteReader());

    }

 

}

dataGridViewX1.DataSource = table;

//总列数

int columnCount = dataGridViewX1.Columns.Count;

//创建Excel对象

Excel.Application excelApp = new Excel.ApplicationClass();

//新建工作簿

Excel.Workbook workBook = excelApp.Workbooks.Add(true);

//新建工作表

Excel.Worksheet worksheet = workBook.ActiveSheet as Excel.Worksheet;

//设置标题

Excel.Range titleRange = worksheet.get_Range(

    worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]);     //选取单元格

titleRange.Merge(true);                                          //合并单元格

titleRange.Value2 = "犯罪人员信息";                              //设置单元格内文本

titleRange.Font.Name = "黑体";                                   //设置字体

titleRange.Font.Size = 20;                                       //字体大小

titleRange.Font.Bold = true;                                     //加粗显示

titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;  //水平居中

titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;    //垂直居中

titleRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;   //设置边框

titleRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;        //边框常规粗细

 

//设置表头

for (int i = 0; i < columnCount; i++)

{

    Excel.Range headRange = worksheet.Cells[2, i + 1] as Excel.Range; //获取表头单元格

    headRange.Value2 = dataGridViewX1.Columns[i].HeaderText;          //设置单元格文本

    headRange.Font.Name = "黑体";                                     //设置字体

    headRange.Font.Size = 14;                                         //字体大小

    headRange.Font.Bold = true;                                       //加粗显示

    headRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;    //水平居中

    headRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;      //垂直居中

    headRange.ColumnWidth = dataGridViewX1.Columns[i].Width / 8;      //设置列宽

    headRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;     //设置边框

    headRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;         //边框常规粗细

}

 

//填充数据

for (int i = 0; i < dataGridViewX1.Rows.Count; i++)

{

    for (int j = 0; j < dataGridViewX1.Columns.Count; j++)

    {

        Excel.Range contentRange = worksheet.Cells[i + 3, j + 1] as Excel.Range; //获取单元格

        contentRange.Value2 = dataGridViewX1[j, i].Value;                        //设置单元格文本

        contentRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;         //设置边框

        contentRange.Borders.Weight = Excel.XlBorderWeight.xlMedium;             //边框常规粗细

        contentRange.WrapText = true;                                            //自动换行

    }

}

 

//设置每列格式

//身份证列

Excel.Range identityIdRange = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[dataGridViewX1.RowCount + 3, 1]);

identityIdRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;   //对其方式

identityIdRange.NumberFormatLocal = "0";                             //格式化文本

//生日列

Excel.Range birthdayRange = worksheet.get_Range(worksheet.Cells[3, 3], worksheet.Cells[dataGridViewX1.RowCount + 3, 3]);

birthdayRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //对其方式

birthdayRange.NumberFormatLocal = "yyyy-MM-dd";                    //格式化文本

 

//设置Excel可见

excelApp.Visible = true;

//显示打印预览

worksheet.PrintPreview(true);