您身边的软件定制专家--9年开发经验为您护航

18678812288
0531-88887250

网站首页 关于我们 开发优势 产品展示
合作企业 新闻动态 联系我们 电话联系

C# 操作excel和xml

文章作者：济南软件开发 时间：2016年12月20日

需要添加COM组件 Interop.Excel.dll,Interop.Microsoft.Office.Core.dll

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Drawing;
using System.IO;
using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;

public class ImportExportToExcel
{
private string strConn;
private System.Windows.Forms.OpenFileDialog openFileDlg = new System.Windows.Forms.OpenFileDialog();
private System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog();

public ImportExportToExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
this.openFileDlg.DefaultExt = "xls";
this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";

this.saveFileDlg.DefaultExt = "xls";
this.saveFileDlg.Filter = "Excel文件 (*.xls)|*.xls";

}

// 从Excel文件导入到DataSet#region 从Excel文件导入到DataSet
// ///


// /// 从Excel导入文件
// ///
// /// Excel文件名
// /// 返回DataSet
// public DataSet ImportFromExcel(string strExcelFileName)
// {
// return doImport(strExcelFileName);
// }
///
/// 从选择的Excel文件导入
///
/// DataSet
public DataSet ImportFromExcel()
{
DataSet ds = new DataSet();
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
ds = doImport(openFileDlg.FileName);
return ds;
}
/**/
///
/// 从指定的Excel文件导入
///
/// Excel文件名
/// 
public DataSet ImportFromExcel(string strFileName)
{
DataSet ds = new DataSet();
ds = doImport(strFileName);
return ds;
}
///
/// 执行导入
///
/// 文件名
/// DataSet
private DataSet doImport(string strFileName)
{
if (strFileName == "") return null;

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFileName + ";" +
"Extended Properties=Excel 8.0;";
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

DataSet ExcelDs = new DataSet();
try
{
ExcelDA.Fill(ExcelDs, "ExcelInfo");

}
catch (Exception err)
{
System.Console.WriteLine(err.ToString());
}
return ExcelDs;



}

//从DataSet到出到Excel#region 从DataSet到出到Excel
///
/// 导出指定的Excel文件
///
/// 要导出的DataSet
/// 要导出的Excel文件名
public void ExportToExcel(DataSet ds, string strExcelFileName)
{
if (ds.Tables.Count == 0 || strExcelFileName == "") return;
doExport(ds, strExcelFileName);


}
///
/// 导出用户选择的Excel文件
///
/// DataSet
public void ExportToExcel(DataSet ds)
{
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExport(ds, saveFileDlg.FileName);

}
///
/// 执行导出
///
/// 要导出的DataSet
/// 要导出的文件名
private void doExport(DataSet ds, string strExcelFileName)
{

Excel.Application excel = new Excel.Application();

// Excel.Workbook obj=new Excel.WorkbookClass();
// obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);

int rowIndex = 1;
int colIndex = 0;

excel.Application.Workbooks.Add(true);
//对该死的提示进行处理
excel.AlertBeforeOverwriting = false;//一定要写不要会弹出修改的的提示
if (File.Exists(strExcelFileName+".XLS")) { File.Delete(strExcelFileName+".XLS"); }

System.Data.DataTable table = ds.Tables[0];
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}

foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
excel.Visible = false;
// excel.Sheets[0] = "sss";
excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS",Excel.XlFileFormat.xlExcel9795,null, null, false,false,Excel.XlSaveAsAccessMode.xlNoChange,null, null, null,null,true);
//excel.ActiveWorkbook.SaveAs(

//wkbNew.SaveAs strBookName


//excel.Save(strExcelFileName);
excel.Quit();
excel = null;

GC.Collect();//垃圾回收
}

//从XML导入到Dataset#region 从XML导入到Dataset

///
/// 从选择的XML文件导入
///
/// DataSet
public DataSet ImportFromXML()
{
DataSet ds = new DataSet();
System.Windows.Forms.OpenFileDialog openFileDlg = new System.Windows.Forms.OpenFileDialog();
openFileDlg.DefaultExt = "xml";
openFileDlg.Filter = "xml文件 (*.xml)|*.xml";
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
try { ds.ReadXml(openFileDlg.FileName, System.Data.XmlReadMode.ReadSchema); }
catch { }
return ds;
}
///
/// 从指定的XML文件导入
///
/// XML文件名
/// 
public DataSet ImportFromXML(string strFileName)
{
if (strFileName == "")
return null;
DataSet ds = new DataSet();
try { ds.ReadXml(strFileName, System.Data.XmlReadMode.ReadSchema); }
catch { }
return ds;
}


//从DataSet导出到XML#region 从DataSet导出到XML
///
/// 导出指定的XML文件
///
/// 要导出的DataSet
/// 要导出的XML文件名
public void ExportToXML(DataSet ds, string strXMLFileName)
{
if (ds.Tables.Count == 0 || strXMLFileName == "") return;
doExportXML(ds, strXMLFileName);
}
///
/// 导出用户选择的XML文件
///
/// DataSet
public void ExportToXML(DataSet ds)
{
System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog();
saveFileDlg.DefaultExt = "xml";
saveFileDlg.Filter = "xml文件 (*.xml)|*.xml";
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExportXML(ds, saveFileDlg.FileName);
}

///
/// 执行导出
///
/// 要导出的DataSet
/// 要导出的XML文件名
private void doExportXML(DataSet ds, string strXMLFileName)
{
try
{ ds.WriteXml(strXMLFileName, System.Data.XmlWriteMode.WriteSchema); }
catch (Exception ex)
{ System.Windows.Forms.MessageBox.Show(ex.Message, "Errol"); }
}
}


想要了解更多详情欢迎来电咨询18678812288
登陆网址:www.jnydkj.cn。
联系人:王经理。