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); 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/dh19573681/archive/2008/04/01/2234547.aspx