private void creatExcel()
{
int i=2,j;
Excel.Application excel = new Excel.ApplicationClass();
excel.DefaultFilePath = record_defaultpath;
Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
excel.Cells[1,1]="码";
excel.Cells[1,2]="名";
excel.Cells[1,3]="时间";
excel.Cells[1,4]="位()";
excel.Cells[1,5]="量()";
excel.Cells[1,6]="降()";
foreach(DataRow dr in ds.Tables["check"].Rows)
{
for (j=1;j<=6;j++)
{
excel.Cells[i,j] = dr[j-1] ;
}
i++;
}
workbook.SaveAs(this.currentTime.ToShortDateString(),Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
workbook.Close(false,Type.Missing,Type.Missing);
}
在做一个小项目,需要把一些查询结果导出到Excel,找了一些资料,自己也总结出了一点方法,与大家共享。
一、首先简要描述一下如何操作Excel表
先要添加对Excel的引用。选择项目-〉添加引用-〉COM-〉添加Microsoft Excel 9.0。(不同的office讲会有不同版本的dll文件)。
using Excel;
using System.Reflection;
//产生一个Excel.Application的新进程
Excel.Application app = new Excel.Application();
if (app == null)
{
statusBar1.Text = "ERROR: EXCEL couldn''t be started!";
return ;
}
app.Visible = true; //如果只想用程序控制该excel而不想让用户操作时候,可以设置为false
app.UserControl = true;
Workbooks workbooks =app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); //根据模板产生新的workbook
// _Workbook workbook = workbooks.Add("c://a.xls"); //或者根据绝对路径打开工作簿文件a.xls
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null)
{
statusBar1.Text = "ERROR: worksheet == null";
return;
}
// This paragraph puts the value 5 to the cell G1
Range range1 = worksheet.get_Range("A1", Missing.Value);
if (range1 == null)
{
statusBar1.Text = "ERROR: range == null";
return;
}
const int nCells = 2345;
range1.Value2 = nCells;
二、示例程序
在Visual Studio .NET中建立一个C# WinForm工程.
添加Microsoft Excel Object Library引用:
右键单击Project , 选“添加引用”
在COM 标签项,选中 locate Microsoft Excel Object Library
点确定按钮完成添加引用。 On the View menu, select Toolbox to display the Toolbox. Add two buttons and a check box to Form1.
在Form1上添加一个button1,双击 Button1,添加click事件的代码.把数组里的数据填到Excel表格。
首先添加引用:
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
声明两个类的成员变量
Excel.Application objApp;
Excel._Workbook objBook;
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Workbooks objBooks;
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;
try
{
// Instantiate Excel and start a new workbook.
objApp = new Excel.Application();
objBooks = objApp.Workbooks;
objBook = objBooks.Add( Missing.Value );
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
//Get the range where the starting cell has the address
//m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
range = objSheet.get_Range("A1", Missing.Value);
range = range.get_Resize(5, 5);
if (this.FillWithStrings.Checked == false)
{
//Create an array.
double[,] saRet = new double[5, 5];
//Fill the array.
for (long iRow = 0; iRow < 5; iRow++)
{
for (long iCol = 0; iCol < 5; iCol++)
{
//Put a counter in the cell.
saRet[iRow, iCol] = iRow * iCol;
}
}
//Set the range value to the array.
range.set_Value(Missing.Value, saRet );
}
else
{
//Create an array.
string[,] saRet = new string[5, 5];
//Fill the array.
for (long iRow = 0; iRow < 5; iRow++)
{
for (long iCol = 0; iCol < 5; iCol++)
{
//Put the row and column address in the cell.
saRet[iRow, iCol] = iRow.ToString() + "|" + iCol.ToString();
}
}
//Set the range value to the array.
range.set_Value(Missing.Value, saRet );
}
//Return control of Excel to the user.
objApp.Visible = true;
objApp.UserControl = true;
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );
MessageBox.Show( errorMessage, "Error" );
}
}
4.在Form1上添加一个Button2,双击 Button2,添加click事件的代码,从Excel表格读数据到数组:
private void button2_Click(object sender, System.EventArgs e)
{
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;
try
{
try
{
//Get a reference to the first sheet of the workbook.
objSheets = objBook.Worksheets;
objSheet = (Excel._Worksheet)objSheets.get_Item(1);
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Can't find the Excel workbook. Try clicking Button1 " +
"to create an Excel workbook with data before running Button2.";
MessageBox.Show( errorMessage, "Missing Workbook?");
//You can't automate Excel if you can't find the data you created, so
//leave the subroutine.
return;
}
//Get a range of data.
range = objSheet.get_Range("A1", "E5");
//Retrieve the data from the range.
Object[,] saRet;
saRet = (System.Object[,])range.get_Value( Missing.Value );
//Determine the dimensions of the array.
long iRows;