.xls文件中的十进制数据未被读取和/或转换为.txt文件 [英] decimal data in .xls file not being read and/or converted to .txt file
问题描述
我正在创建一个Web应用程序，该应用程序允许用户选择他们要转换和上载关联文件的报告.我正在使用OleDbCommand和OleDbConnection来访问.xls文件. .xls文件的所有行和列在转换为.txt文件时都会显示，但十进制值的列除外.有人可以看一下并指向我正确的方向吗?谢谢！
这是我到目前为止的代码:
I am creating a web app that allows the user to choose which report they are trying to convert and upload the associated file. I am using the OleDbCommand and OleDbConnection to acess the .xls file. All the rows and columns of the .xls file show up when converted to the .txt file except for a column of decimal values. Can someone take a look and point me in the right direction? Thank you!
Here is the code I have so far:
if (ddlType.SelectedValue == "direct-debit")
{
string filename = FileUpload1.PostedFile.FileName.ToString();
if (filename != "")
{
FileInfo file = new FileInfo(filename);
if (file.Exists)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Open(@"C:\directdebitTEST\directdebitTEST.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//The connection string to the excel file
string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\directdebitTEST\directdebitTEST.xls;Extended Properties=Excel 8.0";
//The connection to that file
OleDbConnection conn = new OleDbConnection(connstr);
//The query
string strSQL = "SELECT * FROM [Sheet1$]";
//The command
OleDbCommand cmd = new OleDbCommand(strSQL, conn);
DataTable dt = new DataTable();
conn.Open();
try
{
OleDbDataReader dr1 = cmd.ExecuteReader();
StreamWriter sw = new StreamWriter(@"C:\directdebitTEST\directdebitExport.txt");
if (dr1.Read())
{
dt.Load(dr1);
}
int iColCount = 3;
//dt.Columns.Count;
for (int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if (i < iColCount - 1)
{
sw.Write("\t");
}
}
sw.Write(sw.NewLine);
// Now write all the rows.
foreach (DataRow dr in dt.Rows)
{
if (dr.Field<string>("F1") != "User Group" && dr.Field<string>("F1") != "Batch Totals" && dr.Field<string>("F1") != "Grand Totals"
&& dr.Field<string>("F1") != "Batch ID:" && dr.Field<string>("F1") != "Item Count")
{
sw.Write("6 ");
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
sw.Write(dr[i].ToString());
}
if (i < iColCount - 1)
{
sw.Write("\t");
}
}
sw.Write(sw.NewLine);
}
}
sw.Write("9");
sw.Close();
}
catch (OleDbException caught)
{
Response.Write(caught.Message);
}
finally
{
conn.Close();
}
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
string fileName = "directdebitExport.txt";
string filePath = @"C:\directdebitTEST\directdebitExport.txt";
Response.Clear();
Response.AppendHeader("content-disposition",
"attachment; filename=" + fileName);
Response.ContentType = "application/octet-stream";
Response.WriteFile(filePath);
Response.Flush();
Response.End();
}
}
else
{
Response.Write("This file does not exist.");
}
}
推荐答案
; //命令 OleDbCommand cmd =新的OleDbCommand(strSQL，conn); DataTable dt = new DataTable(); conn.Open(); 尝试 { OleDbDataReader dr1 = cmd.ExecuteReader(); StreamWriter sw =新的StreamWriter(@"C:\ directdebitTEST \ directdebitExport.txt"); 如果(dr1.Read()) { dt.Load(dr1); } int iColCount = 3; //dt.Columns.Count; 为(int i = 0; i< iColCount; i ++) { sw.Write(dt.Columns [i]); 如果(i< iColCount-1) { sw.Write("\ t"); } } sw.Write(sw.NewLine); //现在写所有行. foreach(dt.rows中的DataRow dr) { if(dr.Field< string>("F1")！=用户组"&& dr.Field< string>("F1")！=批处理总计"&&&dr.Field< string> ("F1")！=总计" && dr.Field< string>("F1")！=批次ID:"&& dr.Field< string>("F1")！=项目计数") { sw.Write("6"); 为(int i = 0; i< iColCount; i ++) { 如果(！Convert.IsDBNull(dr [i])) { sw.Write(dr [i] .ToString()); } 如果(i< iColCount-1) { sw.Write("\ t"); } } sw.Write(sw.NewLine); } } sw.Write("9"); sw.Close(); } 捕获(捕获的是OleDbException) { Response.Write(caught.Message); } 最后 { conn.Close(); } xlWorkBook.Close(true，misValue，misValue); xlApp.Quit(); 字符串fileName ="directdebitExport.txt"; 字符串filePath = @"C:\ directdebitTEST \ directdebitExport.txt"; Response.Clear(); Response.AppendHeader("content-disposition"， "attachment; filename =" + fileName); Response.ContentType =应用程序/八位字节流"; Response.WriteFile(filePath); Response.Flush(); Response.End(); } } 别的 { Response.Write(此文件不存在."); } }
"; //The command OleDbCommand cmd = new OleDbCommand(strSQL, conn); DataTable dt = new DataTable(); conn.Open(); try { OleDbDataReader dr1 = cmd.ExecuteReader(); StreamWriter sw = new StreamWriter(@"C:\directdebitTEST\directdebitExport.txt"); if (dr1.Read()) { dt.Load(dr1); } int iColCount = 3; //dt.Columns.Count; for (int i = 0; i < iColCount; i++) { sw.Write(dt.Columns[i]); if (i < iColCount - 1) { sw.Write("\t"); } } sw.Write(sw.NewLine); // Now write all the rows. foreach (DataRow dr in dt.Rows) { if (dr.Field<string>("F1") != "User Group" && dr.Field<string>("F1") != "Batch Totals" && dr.Field<string>("F1") != "Grand Totals" && dr.Field<string>("F1") != "Batch ID:" && dr.Field<string>("F1") != "Item Count") { sw.Write("6 "); for (int i = 0; i < iColCount; i++) { if (!Convert.IsDBNull(dr[i])) { sw.Write(dr[i].ToString()); } if (i < iColCount - 1) { sw.Write("\t"); } } sw.Write(sw.NewLine); } } sw.Write("9"); sw.Close(); } catch (OleDbException caught) { Response.Write(caught.Message); } finally { conn.Close(); } xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); string fileName = "directdebitExport.txt"; string filePath = @"C:\directdebitTEST\directdebitExport.txt"; Response.Clear(); Response.AppendHeader("content-disposition", "attachment; filename=" + fileName); Response.ContentType = "application/octet-stream"; Response.WriteFile(filePath); Response.Flush(); Response.End(); } } else { Response.Write("This file does not exist."); } }
我认为连接字符串是错误的.
替换:
In my opinion, the connection string is wrong.
Replace:
string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\directdebitTEST\directdebitTEST.xls;Extended Properties=Excel 8.0;";
与:
with:
string connstr = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\directdebitTEST\directdebitTEST.xls;Extended Properties=Excel 8.0;HDR=no;IMEX=0;";
需要使用特定于语言的转义语法来转义字符串中的配额.
c#，c ++ \"
VB6，VBScript "
xml (web.config等) & quot;
或可以使用一个配额''.
"HDR =是；"表示第一行包含列名，而不是数据. "HDR =否；"表示相反.
"IMEX = 1；"告诉驱动程序始终以文本形式读取混合"(数字，日期，字符串等)数据列.请注意，此选项可能会影响excel工作表写入访问权限.
SQL语法从[Sheet One
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++ \"
VB6, VBScript ""
xml (web.config etc) "
or maybe use a single quota ''.
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.
SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One
中选择[SELECT [Column Name One]，[Column Name Two]". IE. excel工作表名称，后跟"
". I.e. excel worksheet name followed by a "
这篇关于.xls文件中的十进制数据未被读取和/或转换为.txt文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
