1 在C#中创建Excel文件
用C#创建excel文件时,需要先在项目中添加引用“Microsoft Excel 12.0Object Library”。具体操作如下图所示。
代码:
using System;
using System.Windows.Forms;
using Excel =Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] ="http://csharp.net-informations.com";
xlWorkBook.SaveAs("csharp-Excel.xls",Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue,misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the filec:\\csharp-Excel.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("ExceptionOccured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
2 打开Excel文件
using System.Windows.Forms;
using Excel =Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue =System.Reflection.Missing.Value;
xlApp = newExcel.ApplicationClass();
xlWorkBook =xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5,"", "", true,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false,false, 0, true, 1, 0);
xlWorkSheet =(Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
MessageBox.Show(xlWorkSheet.get_Range("A1","A1").Value2.ToString());
xlWorkBook.Close(true, misValue,misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable torelease the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
3 格式化Excel文件
using System;
using System.Windows.Forms;
using Excel =Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue =System.Reflection.Missing.Value;
Excel.Range chartRange ;
xlApp = newExcel.ApplicationClass();
xlWorkBook =xlApp.Workbooks.Add(misValue);
xlWorkSheet =(Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[4, 2] ="";
xlWorkSheet.Cells[4, 3] ="Student1";
xlWorkSheet.Cells[4, 4] ="Student2";
xlWorkSheet.Cells[4, 5] ="Student3";
xlWorkSheet.Cells[5, 2] ="Term1";
xlWorkSheet.Cells[5, 3] ="80";
xlWorkSheet.Cells[5, 4] ="65";
xlWorkSheet.Cells[5, 5] ="45";
xlWorkSheet.Cells[6, 2] ="Term2";
xlWorkSheet.Cells[6, 3] ="78";
xlWorkSheet.Cells[6, 4] ="72";
xlWorkSheet.Cells[6, 5] ="60";
xlWorkSheet.Cells[7, 2] ="Term3";
xlWorkSheet.Cells[7, 3] ="82";
xlWorkSheet.Cells[7, 4] ="80";
xlWorkSheet.Cells[7, 5] ="65";
xlWorkSheet.Cells[8, 2] ="Term4";
xlWorkSheet.Cells[8, 3] ="75";
xlWorkSheet.Cells[8, 4] ="82";
xlWorkSheet.Cells[8, 5] ="68";
xlWorkSheet.Cells[9, 2] ="Total";
xlWorkSheet.Cells[9, 3] ="315";
xlWorkSheet.Cells[9, 4] ="299";
xlWorkSheet.Cells[9, 5] ="238";
xlWorkSheet.get_Range("b2", "e3").Merge(false);
chartRange =xlWorkSheet.get_Range("b2", "e3");
chartRange.FormulaR1C1 = "MARKLIST";
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
chartRange =xlWorkSheet.get_Range("b4", "e4");
chartRange.Font.Bold = true;
chartRange =xlWorkSheet.get_Range("b9", "e9");
chartRange.Font.Bold = true;
chartRange =xlWorkSheet.get_Range("b2", "e9");
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,Excel.XlColorIndex.xlColorIndexAutomatic);
xlWorkBook.SaveAs("csharp.net-informations.xls",Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue,misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
MessageBox.Show ("File created!");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable torelease the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
4 在Excel文件中插入图片
using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue =System.Reflection.Missing.Value;
xlApp = newExcel.ApplicationClass();
xlWorkBook =xlApp.Workbooks.Add(misValue);
xlWorkSheet =(Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add some text
xlWorkSheet.Cells[1, 1] ="http://csharp.net-informations.com";
xlWorkSheet.Cells[2, 1] = "Addingpicture in Excel File";
xlWorkSheet.Shapes.AddPicture("C:\\csharp-xl-picture.JPG",Microsoft.Office.Core.MsoTriState.msoFalse,Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 50, 300, 45);
xlWorkBook.SaveAs("csharp.net-informations.xls",Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue,misValue);
xlWorkBook.Close(true, misValue,misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
MessageBox.Show ("File created!");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable torelease the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
5 在Excel文件中创建图表
using System;
using System.Windows.Forms;
using Excel =Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue =System.Reflection.Missing.Value;
xlApp = newExcel.ApplicationClass();
xlWorkBook =xlApp.Workbooks.Add(misValue);
xlWorkSheet =(Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[1, 1] ="";
xlWorkSheet.Cells[1, 2] ="Student1";
xlWorkSheet.Cells[1, 3] ="Student2";
xlWorkSheet.Cells[1, 4] ="Student3";
xlWorkSheet.Cells[2, 1] ="Term1";
xlWorkSheet.Cells[2, 2] ="80";
xlWorkSheet.Cells[2, 3] ="65";
xlWorkSheet.Cells[2, 4] ="45";
xlWorkSheet.Cells[3, 1] ="Term2";
xlWorkSheet.Cells[3, 2] ="78";
xlWorkSheet.Cells[3, 3] ="72";
xlWorkSheet.Cells[3, 4] ="60";
xlWorkSheet.Cells[4, 1] ="Term3";
xlWorkSheet.Cells[4, 2] ="82";
xlWorkSheet.Cells[4, 3] = "80";
xlWorkSheet.Cells[4, 4] ="65";
xlWorkSheet.Cells[5, 1] ="Term4";
xlWorkSheet.Cells[5, 2] ="75";
xlWorkSheet.Cells[5, 3] ="82";
xlWorkSheet.Cells[5, 4] ="68";
Excel.Range chartRange ;
Excel.ChartObjects xlCharts =(Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart =(Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage =myChart.Chart;
chartRange =xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange,misValue);
chartPage.ChartType =Excel.XlChartType.xlColumnClustered;
xlWorkBook.SaveAs("csharp.net-informations.xls",Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue,misValue);
xlWorkBook.Close(true, misValue,misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel filecreated , you can find the file c:\\csharp.net-informations.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("ExceptionOccured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
6 用OLEDB读取Excel文件
using System;
using System.Drawing;
using System.Windows.Forms;
using Excel =Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
System.Data.OleDb.OleDbConnection MyConnection ;
System.Data.DataSet DtSet ;
System.Data.OleDb.OleDbDataAdapterMyCommand ;
MyConnection = newSystem.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;DataSource='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
MyCommand = newSystem.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]",MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = newSystem.Data.DataSet();
MyCommand.Fill(DtSet);
dataGridView1.DataSource =DtSet.Tables[0];
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}