C# Excel操作示例 - 帅华
来源中国教程网
http://www.cnjiaocheng.com
http://www.cnjiaocheng.com
DataTable table = new DataTable();
http://www.cnjiaocheng.com
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;
http://www.cnjiaocheng.com
//创建Excel对象
Excel.Application excelApp = new Excel.ApplicationClass();
//新建工作簿
Excel.Workbook workBook = excelApp.Workbooks.Add(true);
//新建工作表
http://www.cnjiaocheng.com
Excel.Worksheet worksheet = workBook.ActiveSheet as Excel.Worksheet;
//设置标题
Excel.Range titleRange = worksheet.get_Range(
http://www.cnjiaocheng.com
worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]); //选取单元格
http://www.cnjiaocheng.com
titleRange.Merge(true); //合并单元格
titleRange.Value2 = "犯罪人员信息"; //设置单元格内文本
titleRange.Font.Name = "黑体"; //设置字体
titleRange.Font.Size = 20; //字体大小
titleRange.Font.Bold = true; //加粗显示
http://www.cnjiaocheng.com
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; //获取表头单元格
http://www.cnjiaocheng.com
headRange.Value2 = dataGridViewX1.Columns[i].HeaderText; //设置单元格文本
http://www.cnjiaocheng.com
headRange.Font.Name = "黑体"; //设置字体
headRange.Font.Size = 14; //字体大小
http://www.cnjiaocheng.com
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; //边框常规粗细
}
来源中国教程网
//填充数据
http://www.cnjiaocheng.com
for (int i = 0; i < dataGridViewX1.Rows.Count; i++)
来源中国教程网
{
http://www.cnjiaocheng.com
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; //设置边框
http://www.cnjiaocheng.com
contentRange.Borders.Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
contentRange.WrapText = true; //自动换行
}
http://www.cnjiaocheng.com
}
//设置每列格式
//身份证列
Excel.Range identityIdRange = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[dataGridViewX1.RowCount + 3, 1]);
http://www.cnjiaocheng.com
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; //对其方式
http://www.cnjiaocheng.com
birthdayRange.NumberFormatLocal = "yyyy-MM-dd"; //格式化文本
//设置Excel可见
excelApp.Visible = true;
//显示打印预览
worksheet.PrintPreview(true);
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=2234547 http://www.cnjiaocheng.com
