prague12

透過Excel的內部ode交換 將XLS轉換成CSV輸出

//'------------------------------------------

namespace XLStoCSV
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
string sourceFile, worksheetName, targetFile;
sourceFile = "source.xls";
worksheetName = "sheet1";
targetFile = "targetAA.csv";

if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
sourceFile = openFileDialog1.FileName;
}


convertExcelToCSV(sourceFile, worksheetName, targetFile);

MessageBox.Show("OK");
}

//==================
static void convertExcelToCSV(string sourceFile, string worksheetName, string targetFile)
{

// string strConn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = ' " + sourceFile + " ' " + ";Extended Properties=Excel 8.0 ; HDR=Yes;IMEX=1\" ";
// sourceFile = "new_N40SM1.1.xls";
string strConn = " Provider='Microsoft.Jet.OLEDB.4.0';"+ "Data Source='" + sourceFile +"';Extended Properties='Excel 8.0;IMEX=1;HDR=NO;' ";
OleDbConnection conn = null;
StreamWriter wrtr = null;
OleDbCommand cmd = null;
OleDbDataAdapter da = null;
try
{
conn = new OleDbConnection(strConn);
conn.Open();

cmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
cmd.CommandType = CommandType.Text;
wrtr = new StreamWriter(targetFile);

da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

for (int x = 0; x {
string rowString = "";
for (int y = 0; y {
rowString += "\"" + dt.Rows[x][y].ToString() + "\",";
}
wrtr.WriteLine(rowString);
}
//Console.WriteLine();
//Console.WriteLine("Done! Your " + sourceFile + " has been converted into " + targetFile + ".");
//Console.WriteLine();

}
catch (Exception exc)
{
MessageBox .Show ( exc.ToString());

}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
wrtr.Close();
wrtr.Dispose();
}
}

//=================
}
}