资料来自网络,感谢原创者的分享。
C#在操作Excel之前,需要添加引用。 (解决方案资源管理器视图:工程名->右键->添加->添加引用,或者 工程-引用->右键->添加引用)
Microsoft Office 11.0 Object Library ,Microsoft Excel 11.0 Object Library 。具体版本号与电脑上安装的office版本有关。
添加using
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;
若提示 Worksheet 等变量没有定义,请使用 Excel.Worksheet 的形式。
Provider=Microsoft.Jet.OleDb.4.0 -> Provider=Microsoft.Ace.OleDb.12.0
Extended Properties='Excel 8.0... -> Extended Properties='Excel 12.0... //或者14.0(office2010的版本)
Excel 操作类:
说明:ImportExcel函数,将Excel表的第一行作为列名,从第二行开始作为数据,所以,第一行不要是标题之类的与“表格”无关的内容。
class ExcelIO
{
private int _ReturnStatus;
private string _ReturnMessage;
/// <summary>
/// 执行返回状态
/// </summary>
public int ReturnStatus
{
get { return _ReturnStatus; }
}
/// <summary>
/// 执行返回信息
/// </summary>
public string ReturnMessage
{
get { return _ReturnMessage; }
}
public ExcelIO()
{
}
/// <summary>
/// 导入EXCEL到DataSet
/// </summary>
/// <param name="fileName">Excel全路径文件名</param>
/// <returns>导入成功的DataSet</returns>
public DataSet ImportExcel(string fileName)
{
//判断是否安装EXCEL
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "无法创建Excel对象,可能您的计算机未安装Excel";
return null;
}
//判断文件是否被其他进程使用
Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
}
catch
{
_ReturnStatus = -1;
_ReturnMessage = "Excel文件处于打开状态,请保存关闭";
return null;
}
//获得所有Sheet名称
int n = workbook.Worksheets.Count;
string[] SheetSet = new string[n];
System.Collections.ArrayList al = new System.Collections.ArrayList();
for (int i = 1; i <= n; i++)
{
SheetSet[i - 1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
}
//释放Excel相关对象
workbook.Close(null, null, null);
xlApp.Quit();
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
//把EXCEL导入到DataSet
DataSet ds = new DataSet();
string connStr = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + fileName + ";Extended Properties=Excel 12.0";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
OleDbDataAdapter da;
for (int i = 1; i <= n; i++)
{
string sql = "select * from [" + SheetSet[i - 1] + "$] ";
da = new OleDbDataAdapter(sql, conn);
da.Fill(ds, SheetSet[i - 1]);
da.Dispose();
}
conn.Close();
conn.Dispose();
}
return ds;
}
/// <summary>
/// 把DataTable导出到EXCEL
/// </summary>
/// <param name="reportName">报表名称</param>
/// <param name="dt">数据源表</param>
/// <param name="saveFileName">Excel全路径文件名</param>
/// <returns>导出是否成功</returns>
public bool ExportExcel(string reportName, DataTable dt, string saveFileName)
{
if (dt == null)
{
_ReturnStatus = -1;
_ReturnMessage = "数据集为空!";
return false;
}
bool fileSaved = false;
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "无法创建Excel对象,可能您的计算机未安装Excel";
return false;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size = 10;
Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
worksheet.Cells[1, 1] = reportName;
//合并单元格
Excel.Range firstRow = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 10]);
firstRow.Merge(firstRow.MergeCells);
firstRow.Font.Size = 15;
firstRow.Font.Bold = true;
firstRow.Font.Color = Color.Brown;
firstRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//((Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;
//((Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;
//((Excel.Range)worksheet.Columns[1]).ColumnWidth = 40;
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
range.EntireColumn.AutoFit();
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
//保存文件
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = "导出文件时出错,文件可能正被打开!\n" + ex.Message;
}
}
else
{
fileSaved = false;
}
//释放Excel对应的对象
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return fileSaved;
}
/// <summary>
/// 新建一个空白的Excel文件
/// </summary>
/// <param name="sExcelFilePath">全路径文件名</param>
/// <return name="false">创建失败</return>
/// <return name="true">创建成功</return>
public static void CreatExcelFile(string sExcelFilePath)
{
object missing = System.Reflection.Missing.Value;
Excel.ApplicationClass myExcel = new Excel.ApplicationClass();
Excel._Workbook xBk; //工作薄
Excel._Worksheet xSt; //工作Sheet
xBk = myExcel.Workbooks.Add(true);
for (int i = 3; i >0; i--)
{
xSt = (Excel._Worksheet)xBk.ActiveSheet;
xSt.Name = "sheet" + i;
if (i > 1)
{
myExcel.Sheets.Add(missing, missing, 1, Excel.XlSheetType.xlWorksheet);
}
}
//myExcel.Visible = true;
xBk.SaveAs(sExcelFilePath, missing, missing,
missing, missing, missing, Excel.XlSaveAsAccessMode.xlShared,
missing, missing, missing, missing, missing);
myExcel.Quit();
}
}
CSV文件操作类
public interface ICSVWriterReader
{//define a interface
string CSVFile { get; set; } //csv file
DataTable Read();//read csv to DataTable.
bool Write(DataTable dt); //convert datatable to csv
}
class CSVDataTable : ICSVWriterReader
{
private string _csvFile;
public CSVDataTable(string csvFile)
{
this._csvFile = csvFile;
}
#region ICSVWriterReader Members
public string CSVFile
{
get{ return _csvFile; }
set{ _csvFile=value ;}
}
public DataTable Read()
{
FileInfo fi = new FileInfo(this._csvFile);
if (fi == null || !fi.Exists) return null;
StreamReader reader = new StreamReader(this._csvFile);
string line = string.Empty; int lineNumber = 0;
DataTable dt = new DataTable();
while ((line = reader.ReadLine()) != null)
{
if (lineNumber == 0)
{//Create Tole
dt = CreateDataTable(line);
if (dt.Columns.Count == 0) return null;
}
else
{
bool isSuccess = CreateDataRow(ref dt, line);
if (!isSuccess) return null;
}
lineNumber++;
}
return dt;
}
public bool Write(DataTable dt)
{
FileInfo fi = new FileInfo(this._csvFile);
if (fi == null || !fi.Exists) return false;
if (dt == null || dt.Columns.Count == 0 || dt.Rows.Count == 0) return false;
StreamWriter writer = new StreamWriter(this._csvFile);
//writer.AutoFlush = true;
string line = string.Empty;
line = CreateTitle(dt);
writer.WriteLine(line);
foreach (DataRow dr in dt.Rows)
{
line = CretreLine(dr);
writer.WriteLine(line);
}
writer.Flush();
return true;
}
private DataTable CreateDataTable(string line)
{
DataTable dt = new DataTable();
foreach (string field in
line.Split(FormatSplit, StringSplitOptions.None))
{
dt.Columns.Add(field);
}
return dt;
}
private bool CreateDataRow(ref DataTable dt, string line)
{
DataRow dr = dt.NewRow();
string[] fileds = line.Split(FormatSplit, StringSplitOptions.None);
if (fileds.Length == 0 || fileds.Length != dt.Columns.Count) return false;
for (int i = 0; i < fileds.Length; i++)
{
dr[i] = fileds[i];
}
dt.Rows.Add(dr);
return true;
}
private char[] FormatSplit
{
get { return new char[] { ',' }; }
}
private string CreateTitle(DataTable dt)
{
string line = string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
line += string.Format("{0}{1}", dt.Columns[i].ColumnName, FormatSplit[0].ToString());
}
line.TrimEnd(FormatSplit[0]);
return line;
}
private string CretreLine(DataRow dr)
{
string line = string.Empty;
for (int i = 0; i < dr.ItemArray.Length; i++)
{
line += string.Format("{0}{1}", dr[i], FormatSplit[0].ToString());
}
line.TrimEnd(FormatSplit[0]);
return line;
}
#endregion ICSVWriterReader Members
}