外行人高分求助：c# excel读取数据并写入txt问题

2024-12-02 07:37:10

推荐回答（3个）

回答1：

不知道上面的老大回答行不行！如果不行你再看我这个吧！

看的好晕呀！我这样描述一下你看对不！
就是你有一个excel文件里面有四列！想把这四列导入到txt里！

我之前做过一个读取excel的东西把excel读取到dataset里然后再进行操作！你要是觉得我说的差不多！就百度Hi我！

已经做好了，百度Hi给你发消息没反应

代码贴在这了看到了Hi我我把源程序给你

DataSet ds = new DataSet();
private void btnSPath_Click(object sender, EventArgs e)
{
OleDbConnection conn = null;
try
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = ("Excel 文件(*.xls)|*.xls");
if (openFile.ShowDialog() == DialogResult.OK)
{

string filename = openFile.FileName;

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
conn = new OleDbConnection(strConn);
string strExcel = "";
OleDbDataAdapter myCommand = null;

strExcel = "select * from [sheet1$]";
conn.Open();
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, "dtSource");
mySource.DataSource = ds;
mySource.DataMember = "dtSource";
dgvExcelInfo.DataSource = mySource;
}
}
catch (Exception ex) { ds = null; MessageBox.Show("查看出错：" + ex.ToString(), "错误信息"); }
finally
{
if (conn != null)
{
conn.Close();
}
}
}

private string ReplaceNum(string sNum)
{
string sReNum = string.Empty;
int iNumLength = 4 - sNum.Length;
for (int i = 0; i < iNumLength; i++)
{
sReNum += "0";
}

return sReNum += sNum;

}

private void btnConvert_Click(object sender, EventArgs e)
{
if (ds != null)
{

FileStream fs = new FileStream("E:\\NewsTxt.txt", FileMode.Append, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs);
string sNum = string.Empty;
string sDateYMD = string.Empty;
string sMDateHMS = string.Empty;
string sNDateHMS = string.Empty;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < 2; j++)
{
if (ds.Tables[0].Rows[i][0].ToString().Trim().Length != 4)
{
sNum = ReplaceNum(ds.Tables[0].Rows[i][0].ToString());
}
else
{
sNum = ds.Tables[0].Rows[i][0].ToString();
}
sMDateHMS = ds.Tables[0].Rows[i][2].ToString().Length >= 8 ? ds.Tables[0].Rows[i][2].ToString().Substring(0, 8) : ds.Tables[0].Rows[i][2].ToString();
sNDateHMS = ds.Tables[0].Rows[i][3].ToString().Length >= 8 ? ds.Tables[0].Rows[i][3].ToString().Substring(0, 8) : ds.Tables[0].Rows[i][3].ToString();
if (sMDateHMS.Length >= 8 && sNDateHMS.Length >= 8)
{

if (j == 0)
{
sw.WriteLine(sNum + ",1," + Convert.ToDateTime(ds.Tables[0].Rows[i][1]).ToString(@"yyyy-MM-dd").Replace('-', '/') + "," + sMDateHMS + "\r");
}
if (j == 1)
{
sw.WriteLine(sNum + ",0," + Convert.ToDateTime(ds.Tables[0].Rows[i][1]).ToString(@"yyyy-MM-dd").Replace('-', '/') + "," + sNDateHMS + "\r");
}
}
else
{
if (sMDateHMS.Length >= 8)
{
if (j == 0)
{
sw.WriteLine(sNum + ",1," + Convert.ToDateTime(ds.Tables[0].Rows[i][1]).ToString(@"yyyy-MM-dd").Replace('-', '/') + "," + sMDateHMS + "\r");
}
}
if (sNDateHMS.Length >= 8)
{
if (j == 1)
{
sw.WriteLine(sNum + ",0," + Convert.ToDateTime(ds.Tables[0].Rows[i][1]).ToString(@"yyyy-MM-dd").Replace('-', '/') + "," + sNDateHMS + "\r");
}
}
}

}
}
sw.Flush();
sw.Close();
MessageBox.Show(@"E:\ConvertTxtByExcel\NewsTxt.txt");
}
else
{
MessageBox.Show("请先选择Excel后再进行转换！");
}
}

回答2：

本人不才，不会用ODBC4EXCEL这种高级技术，以下纯粹手工代码，写的比较乱，好在有些注释，将就看吧。

建立一个VB.NET控制台应用程序，然后C&P以下代码到Module1.vb中
Imports Microsoft.Office.Interop.Excel '在项目属性的引用中添加此引用，注意选择较高的版本。如果你一个都没有请安装Office正式版
Imports System.Text
Imports System.IO
Module Module1
Sub Main()
Dim app = New Application
Dim wb = app.Workbooks.Open("d:\1.xls") '要处理的文件在这里，请自行改名
Dim sh As Worksheet = wb.Sheets(1) '必须放在第一张工作表里
Dim table = New List(Of List(Of String)) 'excel的每个单元格的文本放在这张表里
For Each row As Range In sh.Rows
Dim firstCell As Range = row.Cells(1)
If firstCell.Text = String.Empty Then '从第一行一直处理到空行为止，第一行没有标题，直接放记录
Exit For
End If
Dim lsRow = New List(Of String)
For index As Integer = 1 To 4
Dim cell As Range = row.Cells(index)
lsRow.Add(cell.Text)
Next
table.Add(lsRow)
Next
Dim sb = New StringBuilder '此sb非彼sb，此sb乃StringBuilder
For Each row In table

Dim workNo = String.Format("{0:D4}", Integer.Parse(row(0)))
Dim eventDate = Date.Parse(row(1)).ToString("yyyy/MM/dd")
Dim eventTimeStart = Date.Parse(row(2).Substring(0, 8)).ToString("hh:mm:ss")
Dim eventTimeEnd = Date.Parse(row(3).Substring(0, 8)).ToString("hh:mm:ss")
If Not _isPass(row(2)) Then '一切听LZ的。。。
sb.AppendLine(workNo + ",0," + eventDate + "," + eventTimeStart)
End If
If Not _isPass(row(3)) Then '为了一切LZ。。。
sb.AppendLine(workNo + ",1," + eventDate + "," + eventTimeEnd)
End If
Next
File.WriteAllText("d:\1.txt", sb.ToString) '绕了半天终于出来了，放在这里，自己改名
wb.Close()
app.Quit() '如果你有300G的内存请无视他，不然还是加上吧
End Sub
Private Function _isPass(ByVal context As String) As Boolean
Return context.Contains("旷工") OrElse context.Contains("休息") OrElse context.Contains("未刷卡")
End Function
End Module

回答3：

对应读取excel文件中的信息，我只做过用oledb方式当它是数据库来读取，
string connstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath(serverpath) + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";//连接excel 文件的字符串

System.Data.OleDb.OleDbConnection myole = null;
myole = new System.Data.OleDb.OleDbConnection(connstr);
myole.Open();
System.Data.OleDb.OleDbCommand mycommand =new System.Data.OleDb.OleDbCommand ("select * from [Sheet1$]", myole);
/// 其中的[Sheet1$] 里的Sheet1是excel表名称，注意其格式是必须的。
System.Data.OleDb.OleDbDataReader dr = mycommand.ExecuteReader();
while(dr!=null && dr.Read())
{

//然后这里没一行一行的读，能够把每个单元格的数据读出来，再重新构造一遍又能有什么问题呢？
}