如何将Excel电子表格连接到C#应用程序? [英] How Do I Connect Excel Spreadsheet To A C# Application ?
本文介绍了如何将Excel电子表格连接到C#应用程序?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在创建一个应用程序,我必须从Excel电子表格中读取和更新数据。
解决方案
查看 EPPlus [ ^ ]
如果只是阅读和更新,那很简单,你可以创建自己的操作界面,然后在实现功能的部分,你可以试试这个 excel库 [ ^ ]。下载免费版(可以访问大部分支持的功能),并添加对项目的引用。
阅读Excel:
//创建新工作簿
工作簿工作簿=新工作簿();
//加载文件并导入其数据
workbook.LoadFromFile(@.. \ FandH.xlsx);
编辑单元格:
// 编辑单元格值
sheet.Range [ E2]。值= 00-1-285-7901742;
sheet.Range [ E2]。Style.Font.FontName = Book Antiqua;
sheet.Range [ E2]。Style.Font.Color = Color.DarkOrange ;
< blockquote> Step1:将您的电子表格上传到一些临时文件夹
private void btnUpload_Click( object sender,EventArgs e)
{
string sourceFile = fileDlg.FileName;
string [] fileName = sourceFile.Split(' \\'跨度>);
if (!Directory.Exists(TempDirectory))
{
Directory.CreateDirectory(TempDirectory);
}
string destinationFile = TempDirectory + fileName [fileName.Length - 1 < /跨度>];
if (File.Exists(sourceFile)&&!File.Exists(destinationFile))
{
File.Copy(sourceFile,destinationFile);
MessageBox.Show( 文件已复制..., 通知);
}
else if (!File.Exists(destinationFile))
{
MessageBox.Show( 选择一个文件......, 错误);
}
else
{
MessageBox.Show( 文件已存在..., 通知跨度>);
}
txtBoxFile.Text = string .Empty;
GetAvailableFiles();
}
private void GetAvailableFiles( )
{
cbAllFiles.Items.Clear();
cbSheets.Items.Clear();
cbAllFiles.Text = 选择;
cbSheets.Text = 选择;
string [] allfiles = Directory.GetFiles(TempDirectory);
var fileList =( from f in allfiles 选择 f.Split(' \ \')[f.Split(' \\')。长度 - 1 ])。ToArray< string>();
var extList = new 列表< string>(){ 。xls, .xlsx};
fileList = fileList.Where(f = > extList.Contains(Path.GetExtension(f))&&!f.StartsWith( 〜))。ToArray< string>();
cbAllFiles.Items.AddRange(fileList);
}
注意:
cbAllfiles是组合框的ID
cbSheets是另一个组合框的ID跟踪工作表名称
从cbAllFileName获取工作表名称选择索引更改事件:
private void cbAllFiles_SelectedIndexChanged( object sender,EventArgs e)
{
if (((ComboBox)sender).SelectedIndex != -1)
{
string DB_Path = string .Format(< span class =code-string> {0} {1},TempDirectory,cbAllFiles.SelectedItem.ToString());
string fileExt = Path.GetExtension(DB_Path);
if (fileExt == .xlsx)
ExcelConnection = Provider = Microsoft.ACE.OLEDB.12.0 ;数据源= + DB_Path + ;扩展属性= Excel 12.0;;
else
ExcelConnection = Provider = Microsoft.Jet.OLEDB.4.0; Data Source = + DB_Path + ;扩展属性= \Excel 8.0; HDR =是; IMEX = 1 \;;
尝试
{
OleDbConnection connExcel = new 的OleDbConnection(ExcelConnection);
connExcel.Open();
DataTable schemaTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null );
String sheetName;
List< string> lstsheetNames = new List< string>();
foreach (DataRow row in schemaTable.Rows)
{
sheetName = row.Field< string>( TABLE_NAME)。Trim();
if (sheetName.Length > 1 && sheetName.EndsWith(
Im creating an application where i have to read and update data from Excel Spreadsheet.
解决方案 Look at EPPlus[^]
If just read and update ,that's very simple,you can create your own operation surface,then in the part of realizing function,you can try this excel library[^] .Download a free edition (which can access most of it's supported funtion),and add reference to your project.
Read Excel:
//Create a new workbook
Workbook workbook = new Workbook();
//Load a file and imports its data
workbook.LoadFromFile(@"..\FandH.xlsx");
Edit cell:
//Edit Cell Value
sheet.Range["E2"].Value = "00-1-285-7901742";
sheet.Range["E2"].Style.Font.FontName = "Book Antiqua";
sheet.Range["E2"].Style.Font.Color = Color.DarkOrange;
Other functions as conversion or cell merge is included, more tutorials .
here[^]
Step1:Upload your spreadsheet in some temporary folder
private void btnUpload_Click(object sender, EventArgs e)
{
string sourceFile = fileDlg.FileName;
string[] fileName = sourceFile.Split('\\');
if (!Directory.Exists(TempDirectory))
{
Directory.CreateDirectory(TempDirectory);
}
string destinationFile = TempDirectory + fileName[fileName.Length - 1];
if (File.Exists(sourceFile) && !File.Exists(destinationFile))
{
File.Copy(sourceFile, destinationFile);
MessageBox.Show("File copied...", "Notification");
}
else if (!File.Exists(destinationFile))
{
MessageBox.Show("Select a file...", "Error");
}
else
{
MessageBox.Show("File already exist...", "Notification");
}
txtBoxFile.Text = string.Empty;
GetAvailableFiles();
}
private void GetAvailableFiles()
{
cbAllFiles.Items.Clear();
cbSheets.Items.Clear();
cbAllFiles.Text = "Select";
cbSheets.Text = "Select";
string[] allfiles = Directory.GetFiles(TempDirectory);
var fileList = (from f in allfiles select f.Split('\\')[f.Split('\\').Length - 1]).ToArray<string>();
var extList = new List<string>() { ".xls", ".xlsx" };
fileList = fileList.Where(f => extList.Contains(Path.GetExtension(f)) && !f.StartsWith("~")).ToArray<string>();
cbAllFiles.Items.AddRange(fileList);
}
N.B:
cbAllfiles is ID of a combobox
cbSheets is ID of another combobox which tracks the sheet name
For getting the sheet name from cbAllFileName selected index changed event:
private void cbAllFiles_SelectedIndexChanged(object sender, EventArgs e)
{
if (((ComboBox)sender).SelectedIndex != -1)
{
string DB_Path = string.Format("{0}{1}", TempDirectory, cbAllFiles.SelectedItem.ToString());
string fileExt = Path.GetExtension(DB_Path);
if (fileExt == ".xlsx")
ExcelConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DB_Path + ";Extended Properties=Excel 12.0;";
else
ExcelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DB_Path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
try
{
OleDbConnection connExcel = new OleDbConnection(ExcelConnection);
connExcel.Open();
DataTable schemaTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String sheetName;
List<string> lstsheetNames = new List<string>();
foreach (DataRow row in schemaTable.Rows)
{
sheetName = row.Field<string>("TABLE_NAME").Trim();
if (sheetName.Length > 1 && sheetName.EndsWith("
这篇关于如何将Excel电子表格连接到C#应用程序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文