引用com中的Microsoft.Excel 11.0或12.0 Object Library.
将Microsoft.Office.Interop.Excel属性“嵌入互操作类型”设置为true.
/// <summary>
/// 将excel文件另存csv文件
/// </summary>
/// <param name="excelFileName">Excel文件路径</param>
/// <param name="csvFileName">csv文件路径</param>
public static void SaveAsExcel(string excelFileName, string csvFileName)
{
//定义一个COM中空类型的对象(作用类似于大家所熟悉的null)
object missing = System.Reflection.Missing.Value;
//创建Excel应用程序对象(会帮我们在启动Excel进程)
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook wb = app.Application.Workbooks.Open(excelFileName, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);
//不出现提示,用默认的选择
app.Application.DisplayAlerts = false;
//不运行excel界面
app.Application.Visible = false;
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
try
{
//另存为csv格工,注意Excel.XlFileFormat.xlCSV参数,要另存为别的格式,也是在这里设置
sheet.SaveAs(csvFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, missing, missing, false, false, false, missing, missing, false);
}
finally
{
wb.Close(false, missing, missing);
app.Quit();
}
}
/// <summary>
/// 将csv文件中的数据转成dataTabel
/// </summary>
/// <param name="path">csv路径</param>
/// <returns>DataTable</returns>
public static DataTable CSVToDS(string path)
{
String line;
String[] split = null;
DataTable table = new DataTable("auto");
DataRow row = null;
StreamReader sr = new StreamReader(path, Encoding.Default);
//创建与数据源对应的数据列
line = sr.ReadLine();
split = line.Split(',');
foreach (String colname in split)
{
table.Columns.Add(colname, System.Type.GetType("System.String"));
}
//将数据填入数据表
int j = 0;
while ((line = sr.ReadLine()) != null)
{
j = 0;
row = table.NewRow();
split = line.Split(',');
foreach (String colname in split)
{
row[j] = colname;
j++;
}
table.Rows.Add(row);
}
sr.Close();
//显示数据
return table;
}