如何将Excel电子表格（XLS）保存到数据库（.DBF）文件 [英] How to save an Excel Spreadsheet (XLS) to a Database (.DBF) file
问题描述
您好，
如何导入Excel电子表格并将其保存到.DBF文件中？
我目前有一个打开电子表格并保存到CSV的应用程序，无论如何要改变这个并保存到Database .DBF文件？
我的代码：
Hello,
How would I be able to import an Excel spreadsheet and be able to save this out to a .DBF file?
I currently have an application that opens the spreadsheet and saves to CSV, is there anyway to change this and save to a Database .DBF file?
My Code:
private string _fileLocation;
public SpreadsheetApplication()
{
InitializeComponent();
}
private void btnOpenFile_Click(object sender, EventArgs e)
{
//Opens the openfiledialog box so the user can navigate to the spreadsheet.
if (openfiledialog.ShowDialog() == DialogResult.OK)
{
//Sets the .text property of the text box to be the file selected in the openfiledialog
txtBxFileLocation.Text = openfiledialog.FileName;
//Sets the _fileLocation to be the textbox.text value
_fileLocation = txtBxFileLocation.Text;
}
}
private void btnProcess_Click(object sender, EventArgs e)
{
try
{
if (txtBxNameFile.TextLength <= 0)
{
MessageBox.Show("Please can you enter a name for the new CSV file.", "Please enter a name", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
//Calls the ConvertExcelToCsv method and sets the arguments (Worksheet argument value is hard coded below)
ConvertExcelToCsv(_fileLocation, txtBxNameFile.Text + ".csv");
}
}
catch
{
MessageBox.Show("You need to select your spreadsheet.", "Select Spreadsheet", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void ConvertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1)
{
//Checks if the two files required exist or not and then throws an exception.
if (!File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);
// connection string for XLS
var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
///Connection String for XLSX
//var cnnStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES\"", excelFilePath);
//Creates a new OleDbConnection with an argument of cnnStr
var cnn = new OleDbConnection(cnnStr);
//creates new datatable in memory to store the read excel spreadsheet.
var dt = new DataTable();
try
{
//Opens the new connection called "cnn".
cnn.Open();
var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
string sql = String.Format("select * from [{0}]", worksheet);
var da = new OleDbDataAdapter(sql, cnn);
da.Fill(dt);
}
catch (Exception e)
{
throw e;
}
finally
{
// close resources
cnn.Close();
}
// write out CSV data
using (var wtr = new StreamWriter(csvOutputFile))
{
foreach (DataRow row in dt.Rows)
{
bool firstLine = true;
foreach (DataColumn col in dt.Columns)
{
if (!firstLine) { wtr.Write(","); } else { firstLine = false; }
var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
wtr.Write("\"{0}\"", data);
}
wtr.WriteLine();
}
MessageBox.Show("Spreadsheet has finished processing.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
非常感谢任何帮助。
问候，
Glen
Any help is gratefully appreciated.
Regards,
Glen
推荐答案
嘿那里Glen，
不确定是否有一种简单的直接转换方式，但你可以试试这个：
1.打开Excel作为数据库
Hey there Glen,
Not sure if there is an easy way of direct conversion but you could try this:
1. Open Excel as database
string connection_string = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\file.xls;ReadOnly=1;ColNameHeader=False";
OdbcConnection oConnection = new OdbcConnection(connection_string);
oConnection.Open();
string SQLstr = "SQL code to read your xls file";
OdbcCommand oCommand = new OdbcCommand(SQLstr, oConnection);
OdbcDataReader dReader = oCommand.ExecuteReader();
while (dReader.Read())
{
//read data and form internal store (each loop reads 1 line)
}
2。将其读入内部存储（可能是一些通用的List< t>，其中T可能是您的数据结构）
2. Read it into internal storage (could be some generic List<t>, where T could be your data structure)
List<your_structur> myStorage = new List<your_structure>();
while (dReader.Read())
{
myStorage.Add(dReader.GetValue(0), dReader.GetValue(1), .... );
}</pre></your_structure></your_structur>
3.创建DBF（或其他数据库文件）并插入List< t>中的数据。进入新创建的DBF数据库。
http ：//stackoverflow.com/questions/3641510/how-to-create-a-dbf-file-using-c [ ^ ]
我'除了我需要将所有内容插入MS SQL服务器并且上面定义的方法工作得很好之外，我们完成了与您需要完全相同的操作。希望它能给你一臂之力。
干杯！
Modestas
3. Create the DBF (or other database file) and insert the data from your List<t> into the newly create DBF database.
http://stackoverflow.com/questions/3641510/how-to-create-a-dbf-file-using-c[^]
I've done exactly the same as you need to do except that I needed to insert everything into MS SQL server and the approach defined above worked just fine. Hope it's give you a hand as well.
Cheers!
Modestas
这篇关于如何将Excel电子表格（XLS）保存到数据库（.DBF）文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
