问题描述
您好，
如何导入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
推荐答案
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数据库。
[]
我'除了我需要将所有内容插入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）文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！