简介
用一个小范例示范如何在C#中操作Excel
代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
namespace TestExcel
{
public class CBExcel
{
Excel.Application xlApp; // 声明需要的 Exccel Application 变量
Excel.Workbook xlWorkBook; // 声明 work book
Excel.Worksheet xlWorkSheet;// 声明 excel 的sheet
object misValue = System.Reflection.Missing.Value;
public CBExcel()
{
}
// 设定数据
public void SetData(int i, int j, string data)
{
xlWorkSheet.Cells[i, j] = data;
}
// 插入一个图表到excel分页中
public void SetChart(string start, string end, Excel.XlChartType type)
{
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(start, end); // 设定图表数据的开始与结尾
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = type; // 设定图表的样式
}
// 释放用的函数
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
// 创建一个excel
public void Create()
{
xlApp = new Excel.ApplicationClass(); // 创建一个excel
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // 取得第一页的分页
}
// 储存excel
public void SaveAs()
{
// 这样储存会跳出一个另存文件视窗
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
public void Release()
{
// 释放用到的excel实例
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
}
这是一个经过简单封装过的类,将一些操作的部份封装在一个类中,好方便使用。
使用的代码
private void button1_Click(object sender, EventArgs e)
{
CBExcel excel = new CBExcel(); // 创建CBExcel类
excel.Create(); // 建立Excel
excel.SetData(1, 1, ""); // 设定数据
excel.SetData(1, 2, "Student1");
excel.SetData(1, 3, "Student2");
excel.SetData(1, 4, "Student3");
excel.SetData(2, 1, "Term1");
excel.SetData(2, 2, "80");
excel.SetData(2, 3, "65");
excel.SetData(2, 4, "45");
excel.SetData(3, 1, "Term2");
excel.SetData(3, 2, "81");
excel.SetData(3, 3, "61");
excel.SetData(3, 4, "41");
excel.SetData(4, 1, "Term3");
excel.SetData(4, 2, "82");
excel.SetData(4, 3, "62");
excel.SetData(4, 4, "42");
excel.SetChart("A1", "D4", Excel.XlChartType.xlLine); // 设定图表样式与开始结束位置
excel.SaveAs(); // 储存Excel
excel.Release();// 释放内存
}
excel输出结果
范例下载
http://download.csdn.net/detail/cloudhsu/5072683
2013/02/20新增
支持Excel与CSV数据汇出的代码
因为在实际的需求中,公司中的一些计算机可能不会安装Excel,为了让汇出的工作能正常运作,因此我做了下列的修正
接口:IExcel用来作抽象
interface IExcel
{
void Create();
void SetData(int i, int j, string data);
void SaveAs();
void Release();
}
CBExcel的部份则实践IExcel接口
public class CBExcel : IExcel
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
const string ChartStart = "A1";
string m_ChartEnd;
int m_MaxI;
int m_MaxJ;
public CBExcel()
{
m_ChartEnd = "A1";
m_MaxI = -1;
m_MaxJ = -1;
}
public void SetData(int i, int j, string data)
{
xlWorkSheet.Cells[i, j] = data;
CheckChartEnd(i, j);
}
private void CheckChartEnd(int i, int j)
{
if (m_MaxI <= i)
m_MaxI = i;
if (m_MaxJ <= j)
m_MaxJ = j;
const int a = 0x41;
int word = a + j - 1;
m_ChartEnd = string.Format("{0}{1}", Convert.ToChar(word), m_MaxI);
}
public void SetChart(Excel.XlChartType type)
{
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(500, 80, 350, 350);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range(ChartStart, m_ChartEnd);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = type;
}
public void SetChart(string start, string end, Excel.XlChartType type)
{
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(500, 80, 350, 350);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range(start, end);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = type;
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
public void Create()
{
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
}
public void SaveAs()
{
SetChart(Excel.XlChartType.xlLine);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
public void Release()
{
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
额外在实践一个CBExcelCSV
我用最偷懒的实践手法,直接开个够大的内存来存数据
public class CBExcelCSV : IExcel
{
List<string[]> m_data = new List<string[]>();
const int MAX_COLUMN = 150;
const int MAX_LINE = 2000;
int m_MaxLine;
int m_MaxColumn;
#region IExcel Members
public void Create()
{
m_MaxColumn = -1;
m_MaxLine = MAX_LINE;
for (int i = 0; i < MAX_LINE; i++)
{
m_data.Add(new string[MAX_COLUMN]);
}
}
public void SetData(int i, int j, string data)
{
if (i-1 >= m_MaxLine)
{
m_data.Add(new string[MAX_COLUMN]);
m_MaxLine++;
}
if (m_MaxColumn < j)
{
m_MaxColumn = j;
}
m_data[i-1][j-1] = data;
}
public void SaveAs()
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < m_data.Count; i++ )
{
sb.Append(m_data[i][0]);
for (int j = 1; j < m_MaxColumn; j++)
{
sb.Append(",");
sb.Append(m_data[i][j]);
}
sb.Append("\r\n");
}
save(sb.ToString());
}
void save(string data)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "csv files (*.csv)|*.csv|All files (*.*)|*.*";
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
string fileName = saveFileDialog1.FileName;
File.WriteAllText(fileName, data);
}
}
public void Release()
{
}
#endregion
}
再实践一个CBExcelWrapper来封装,里面有一个IExcel成员,并将所有的执行交给IExcel的实例执行。
public class CBExcelWrapper : IExcel
{
IExcel excel;
bool m_IsExcelInstalled;
public CBExcelWrapper()
{
m_IsExcelInstalled = CheckExcelInstalled();
// 有安装Excel就使用CBExcel类,没有则使用CBExcelCSV类
if(m_IsExcelInstalled)
{
excel = new CBExcel();
}
else
{
excel = new CBExcelCSV();
}
}
bool CheckExcelInstalled()
{
// 这一段代码的重点就是判断到底有没有装Excel
bool installed = false;
Type officeType = Type.GetTypeFromProgID("Excel.Application");
if (officeType != null)
{
installed = true;
}
return installed;
}
#region IExcel Members
public void Create()
{
excel.Create();
}
public void SaveAs()
{
excel.SaveAs();
}
public void Release()
{
excel.Release();
}
public void SetData(int i, int j, string data)
{
excel.SetData(i, j, data);
}
#endregion
}