如何编程填充EXcle表格
如何编程填充EXcle表格？ --------------------编程问答-------------------- 问题描述太模糊了， 可以通过ole db方式访问或通过ADO方式访问excel --------------------编程问答-------------------- 别人的完整例子VS2008：using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Runtime.InteropServices; //for comexcelption
using Excel;
using System.Reflection;
using System.IO;
using Microsoft.Office.Core;
namespace OutputExcel
{
class Program
{
public static SqlConnection getSqlConnection()
{
//第一种 ：SQL 登陆模式
/*
String sqlCon = "server=(local);database=Practice;uid=sa;pwd=p9pip";
return new SqlConnection(sqlCon);
*/
//第二种：windows 登陆模式
String sqlCon = "server=(local);Integrated Security = SSPI;database=Practice";
return new SqlConnection(sqlCon);
}
SqlCommand cmd;
static void Main(string[] args)
{
SqlConnection con = Program.getSqlConnection();
con.Open();
//cmd = new SqlCommand("select * from dbo.Company11", con);
String strSql = "select * from dbo.Company11";
//第一种dataset(假如不知道列数)
SqlDataAdapter myDateAdapter = new SqlDataAdapter(strSql,con);//创建数据适配器
DataSet myDataSet = new DataSet();//建立dataset对象
myDateAdapter.Fill(myDataSet, "Table_Name"); //填充数据
int RowCount = myDataSet.Tables["Table_Name"].Rows.Count;
int ColCount = myDataSet.Tables["Table_Name"].Columns.Count;
//输出到黑屏
for (int indexRow = 0; indexRow < RowCount; indexRow++)
{
for (int indexCol = 0; indexCol < ColCount; indexCol++)
{
String Term = myDataSet.Tables["Table_Name"].Rows[indexRow][indexCol].ToString();
Console.Write("{0} ", Term);
}
Console.WriteLine();
}
//第二种 SqlDataReader (知道列数)
/*
cmd.Connection = con;
cmd.CommandText = strSql;
int RowCount = cmd.ExecuteNonQuery();//影响的行数
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
// Console.WriteLine(sdr["CompanyID"] + "________" + sdr["CmpanyName"] + "________" + sdr["createdate"]);
Console.WriteLine("{0} -- {1} -- {2}",sdr["CompanyID"],sdr["CmpanyName"],sdr["createdate"]);
}
//关闭reader
if(sdr!=null)
sdr.Close();
*/
Excel.Application excelApp = new Excel.ApplicationClass();//创建excel对象
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, ColCount]); //选取单元格
titleRange.Merge(true); //合并单元格
titleRange.Value2 = "Company表的信息"; //设置单元格内文本
titleRange.Font.Name ="黑体"; //设置字体
titleRange.Font.Size = 20; //设置字体大小
titleRange.Font.Bold = true; //加粗显示
titleRange.Font.Underline = true; //设置字体是否有下划线
//设置字体在单元格内的对其方式
titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //水平居中
titleRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //垂直居中
//titleRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //设置边框
titleRange.Borders.Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
titleRange.Borders.LineStyle = 1;//单元格边框线类型(线型,虚线型)
titleRange.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
//指定单元格下边框线粗细,和色彩
titleRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium;
titleRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).ColorIndex = 3;
titleRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb(); //设置单元格的背景色
titleRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick,
Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb()); // 给单元格加边框
//设置表头
for(int i = 0 ;i < ColCount ;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.ColumnWidth = 15; //设置单元格的宽度
headRange.EntireColumn.AutoFit(); //自动调整列宽
headRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //设置边框
headRange.Borders.Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
}
//excelApp.ActiveWindow.FreezePanes = true;//冻结字段滚动条失效
//填充数据
for (int i = 0; i < RowCount; i++)
{
for (int j = 0; j < ColCount; j++)
{
Excel.Range contentRange = workSheet.Cells[i + 3, j + 1] as Excel.Range; //获取单元格
//contentRange.Value2 = dataGridViewX1[j, i].Value; //设置单元格文本
contentRange.Value2 = myDataSet.Tables["Table_Name"].Rows[i][j].ToString(); //设置单元格文本
contentRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; //设置边框
contentRange.Borders.Weight = Excel.XlBorderWeight.xlMedium; //边框常规粗细
contentRange.WrapText = true; //自动换行
contentRange.Interior.ColorIndex = 39; //填充颜色为淡紫色
}
}
////设置每列格式
Excel.Range CompanyID = workSheet.get_Range(workSheet.Cells[3, 1], workSheet.Cells[RowCount + 3, 1]);
CompanyID.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //对其方式
CompanyID.NumberFormatLocal = "0"; //格式化文本
Excel.Range CmpanyName = workSheet.get_Range(workSheet.Cells[3, 2], workSheet.Cells[RowCount + 3, 3]);
CmpanyName.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; //对其方式
CmpanyName.NumberFormatLocal = "0";
Excel.Range createdate = workSheet.get_Range(workSheet.Cells[3, 3], workSheet.Cells[RowCount + 3, 3]);
createdate.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //对其方式
createdate.NumberFormatLocal = "yyyy-MM-dd";
excelApp.Visible = true;//设置Excel可见
//workSheet.PrintPreview(true);//显示打印预览
//关闭连接
if ( con!=null && con.State!=System.Data.ConnectionState.Closed )
con.Close();
//excelApp.Quit();//关闭excel对象
//excelApp = null;
Console.Read();
}
} --------------------编程问答-------------------- excel除了OLEDB 还可以通过非托管的方式直接操作之
补充：.NET技术 , C#
