|
DataTable dt = new DataTable();
private void button2_Click(object sender, EventArgs e)
{
string filepath = string.Empty;
string savePath = string.Empty;
MessageBox.Show("选择excel文件");
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel文件|*.xls";
ofd.Multiselect = false;//只能选单个文件
ofd.ShowReadOnly = true; //设定文件是否只读
if (ofd.ShowDialog() == DialogResult.OK)
{
filepath = ofd.FileName;
}
MessageBox.Show("选择存储的位置");
FolderBrowserDialog dbd = new FolderBrowserDialog();
if (dbd.ShowDialog() == DialogResult.OK)
{
savePath = dbd.SelectedPath;
}
ReadExcel(filepath,savePath);
}
public void ReadExcel(string sExcelFile,string txtSavePath)
{
DataTable ExcelTable;
DataSet ds = new DataSet();
//Excel的连接
//string StrConExcel2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
OleDbConnection objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";" + "Extended Properties=\"Excel 8.0;HDR=NO\"");
objConn.Open();
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
int number = schemaTable.Rows.Count;
for (int n = 0; n < number; n++)
{
string tableName = schemaTable.Rows[n][2].ToString().Trim();//获取 Excel 的表名,默认值是sheet1
string strSql = "select * from [" + tableName + "]";
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
myData.Fill(ds, tableName);//填充数据
objConn.Close();
ExcelTable = ds.Tables[tableName];
int iColums = ExcelTable.Columns.Count;//列数
int iRows = ExcelTable.Rows.Count;//行数
List<string> list_yuming = new List<string>();
string strLine = string.Empty;
for (int i = 0; i < ExcelTable.Rows.Count; i++)
{
for (int j = 0; j < ExcelTable.Columns.Count; j++)
{
if(!string.IsNullOrEmpty(ExcelTable.Rows[j].ToString()))
{
strLine += ExcelTable.Rows[j].ToString() + ",";
}
}
if (!string.IsNullOrEmpty(strLine))
{
strLine = strLine.Substring(0, (strLine.Count() - 1));
list_yuming.Add(strLine);
}
strLine = string.Empty;
}
tableName=tableName.Substring(0,(tableName.Count()-1));
string txtPath = txtSavePath + "\\" + tableName + ".txt";
StreamWriter sw = new StreamWriter(txtPath, true, Encoding.Default);
for (int i = 0; i < list_yuming.Count; i++)
{
sw.WriteLine(list_yuming);
}
sw.Close();
}
MessageBox.Show("ok");
}
|
|