是否可以使用c#来访问excel文件?
问题描述:
嗨简单的问题,我想进入一个文件夹寻找Excel文件。然后进入每个excel文件,并使用c#将红色字体颜色更改为黑色。这可能吗?是否可以使用c#来访问excel文件?
namespace Excel_font_color_change
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
List<string> HtmlPathList = new List<string>();
string folderToSearch;
FolderBrowserDialog fbd = new FolderBrowserDialog();
fbd.ShowNewFolderButton = true;//allow user to create new folders through this dialog
fbd.RootFolder = Environment.SpecialFolder.MyDocuments;//defaults to my computer
System.Windows.Forms.DialogResult dr = fbd.ShowDialog();//make sure user clicks ok
if (dr == DialogResult.OK)
{
folderToSearch = fbd.SelectedPath;//gets folder path
try
{
var allFiles = from files in Directory.EnumerateFiles(folderToSearch, "*.xls*", SearchOption.AllDirectories)
select Path.GetFullPath(files);//gets all files with htm & htm + something for extensions
foreach (string filepath in allFiles)
{
HtmlPathList.Add(filepath);//adds each filepath found to the list
}
}
catch (UnauthorizedAccessException UAEx) { Console.WriteLine(UAEx.Message); }//error handling
catch (PathTooLongException PathEx) { Console.WriteLine(PathEx.Message); }//error handling
Console.WriteLine("1");
}
}
private void button2_Click(object sender, EventArgs e)
{
}
}
这是我到目前为止,我想如果是红色的第二个按钮,采取在HtmlPathList
文件路径和编辑字体颜色为黑色的东西。我现在正在查看How to read data of an Excel file using C#?。
答
看看这个库。它只适用于xlsx。
http://www.microsoft.com/en-us/download/details.aspx?id=5124
如果你想读旧的XLS文件可以使用互操作的组件。
http://www.microsoft.com/en-us/download/details.aspx?id=3508
答
这种解决方案需要对Excel互操作程序集的引用(必须正在执行在后台执行Excel中的效用作为互操作程序集在机器上安装了Excel):
using Microsoft.Office.Interop.Excel;
/// <summary>
/// sets a cell range's font color
/// </summary>
/// <param name="filename"></param>
/// <param name="startCell"></param>
/// <param name="endCell"></param>
/// <param name="color"></param>
public void setCellRangeFontColor(string filename, string startCell, string endCell, string color)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
//xlApp.Visible = true;
//Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Workbook wb = xlApp.Workbooks.Open(filename,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet ws = (Worksheet)wb.Worksheets[1];
if (ws == null)
{
MessageBox.Show("Worksheet could not be created. Check that your office installation and project references are correct.");
}
ws.get_Range(startCell, endCell).Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromName(color));
wb.Close(true, Type.Missing, Type.Missing);
//wb.Save();
xlApp.Quit();
releaseObject(ws);
releaseObject(wb);
releaseObject(xlApp);
}
public static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
//MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
对的,这是可能的。 – 2013-05-03 10:34:02
是的。你到目前为止有多远?您可以枚举目录中的文件,然后使用EPPlus等来打开和修改Excel文件。 – Simon 2013-05-03 10:35:09
任何事情都是可能的......释放你的思想 – RedEyedMonster 2013-05-03 10:35:41