使用分隔符将excel转换为文本格式 [英] Convert excel to text format using delimiters

问题描述

您好b $ b

我需要使用分隔符将Excel转换为文本格式。

我使用Interop编码。但它只需要254列。我正在使用2007 excel格式。

我在Excel中有400列。 Oledb在阅读Excel时只需254列



请有人帮我如何将Excel转换为文本



我尝试了什么：

Hi
I need to convert Excel into text format using delimiters.
I have coded using Interop. But it takes only 254 columns. I am using 2007 excel format.
I have 400 columns in Excel. Oledb takes only 254 columns while reading Excel

Please anyone help me how to convert Excel into text

What I have tried:

tring excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1;';";
                        OleDbConnection excelConn = new OleDbConnection(excelConnStr);
                        excelConn.Open();
                        DataTable dbSchema = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        string strsheetName = "Sheet1$";
                        if (dbSchema != null && dbSchema.Rows.Count > 0)
                        {
                            strsheetName = Convert.ToString(dbSchema.Rows[0]["TABLE_NAME"]);
                        }




                        System.Data.DataTable dtPatterns = new System.Data.DataTable();
                      excelCommand = new OleDbComnd("SELECT * FROM [" + strsheetName + " ]", excelConn);
                        
        
                        excelDataAdapter.Fill(dtPatterns);
                       
                        List<string> xcelData = new List<string>();
                        List<string> ColumnNames = new List<string>();
                        ds.Tables.Add(dtPatterns);
                        totalRowsCount = dtPatterns.Rows.Count;
                        totalColumnsCount = dtPatterns.Columns.Count;

                        this.lblRowsCount.Invoke((MethodInvoker)delegate { this.lblRowsCount.Text = totalRowsCount.ToString(); });
                       
                        for (int c = 0; c < dtPatterns.Columns.Count; c++)
                        {
                            if (c == dtPatterns.Columns.Count - 1)
                            {
                                SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString());
                            }
                            else
                            {
                                SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString() + sValue.ToString());
                            }
                        }
                        SB1.Append("\r\n");
                        for (int d = 0; d < dtPatterns.Rows.Count; d++)
                        {
                            this.toolStripStatusLabel1.Text = string.Format("Processing Please Wait ...{0} of {1}", (count + 1), totalRowsCount);
                            for (int c = 0; c < dtPatterns.Columns.Count; c++)
                            {
                                int g = dtPatterns.Columns.Count - 1;
                                if (c == g)
                                {
                                    SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString());
                                }
                                else
                                {
                                    SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString() + sValue.ToString());
                                }
                            }
                            SB1.Append("\r\n");
                            count++;
                        }

推荐答案

;
if（dbSchema！= null&& dbSchema.Rows.Count > 0）
{
strsheetName = Convert.ToString（dbSchema.Rows [0] [TABLE_NAME]）;
}




System.Data.DataTable dtPatterns = new System.Data.DataTable（）;
excelCommand = new OleDbComnd（SELECT * FROM [+ strsheetName +]，excelConn）;


excelDataAdapter.Fill（dtPatterns）;

List< string> xcelData = new List< string>（）;
List< string> ColumnNames = new List< string>（）;
ds.Tables.Add（dtPatterns）;
totalRowsCount = dtPatterns.Rows.Coun t;
totalColumnsCount = dtPatterns.Columns.Count;

this.lblRowsCount.Invoke（（MethodInvoker）委托{this.lblRowsCount.Text = totalRowsCount.ToString（）;}）;

for（int c = 0; c< dtPatterns.Columns.Count; c ++）
{
if（c == dtPatterns.Columns.Count - 1）
{
SB1.Append（cValue.ToString（）+ dtPatterns.Columns [c] .ToString（）+ cValue.ToString（））;
}
其他
{
SB1.Append（cValue.ToString（）+ dtPatterns.Columns [c] .ToString（）+ cValue.ToString（）+ sValue.ToString（ ））;
}
}
SB1.Append（\\\\ n）;
for（int d = 0; d< dtPatterns.Rows.Count; d ++）
{
this.toolStripStatusLabel1.Text = string.Format（Processing Please Wait ... {0 } {1}，（count + 1），totalRowsCount）;
for（int c = 0; c< dtPatterns.Columns.Count; c ++）
{
int g = dtPatterns.Columns.Count - 1;
if（c == g）
{
SB1.Append（cValue.ToString（）+ dtPatterns.Rows [d] [c] .ToString（）+ cValue.ToString（）） ;
}
其他
{
SB1.Append（cValue.ToString（）+ dtPatterns.Rows [d] [c] .ToString（）+ cValue.ToString（）+ sValue的ToString（））;
}
}
SB1.Append（\\\\ n）;
count ++;
}

"; if (dbSchema != null && dbSchema.Rows.Count > 0) { strsheetName = Convert.ToString(dbSchema.Rows[0]["TABLE_NAME"]); } System.Data.DataTable dtPatterns = new System.Data.DataTable(); excelCommand = new OleDbComnd("SELECT * FROM [" + strsheetName + " ]", excelConn); excelDataAdapter.Fill(dtPatterns); List<string> xcelData = new List<string>(); List<string> ColumnNames = new List<string>(); ds.Tables.Add(dtPatterns); totalRowsCount = dtPatterns.Rows.Count; totalColumnsCount = dtPatterns.Columns.Count; this.lblRowsCount.Invoke((MethodInvoker)delegate { this.lblRowsCount.Text = totalRowsCount.ToString(); }); for (int c = 0; c < dtPatterns.Columns.Count; c++) { if (c == dtPatterns.Columns.Count - 1) { SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString()); } else { SB1.Append(cValue.ToString() + dtPatterns.Columns[c].ToString() + cValue.ToString() + sValue.ToString()); } } SB1.Append("\r\n"); for (int d = 0; d < dtPatterns.Rows.Count; d++) { this.toolStripStatusLabel1.Text = string.Format("Processing Please Wait ...{0} of {1}", (count + 1), totalRowsCount); for (int c = 0; c < dtPatterns.Columns.Count; c++) { int g = dtPatterns.Columns.Count - 1; if (c == g) { SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString()); } else { SB1.Append(cValue.ToString() + dtPatterns.Rows[d][c].ToString() + cValue.ToString() + sValue.ToString()); } } SB1.Append("\r\n"); count++; }

这是工作表的提供者限制 - 请参阅此处：将数据从Excel文件导入dataTable ：列限制？ [ ^ ]

It's a provider limit for the sheet - see here: Import data from Excel File into a dataTable: column limit?[^]

Quote：

是的我试图合并两个数据集但其合并不是列。

Merge方法不添加列：它将表格模式相同的表行添加到一起。对你来说情况并非如此：你需要添加列。

试试这个：

The Merge method doesn't add columns: it adds table rows together where the table schema is the same. That isn't the case for you: you need to add columns.
Try this:

public static DataTable MergeTables(DataTable t1, DataTable t2)
    {
    // Build combined table columns
    DataTable merged = t1.Clone();                  // Include all columns from t1 in result.
    foreach (DataColumn col in t2.Columns)
        {
        string newColumnName = col.ColumnName;
        merged.Columns.Add(newColumnName, col.DataType);
        }
    // Add all rows from both tables
    var mergedRows = t1.AsEnumerable().Zip(t2.AsEnumerable(), (r1, r2) => r1.ItemArray.Concat(r2.ItemArray).ToArray());
    foreach (object[] rowFields in mergedRows)
        {
        merged.Rows.Add(rowFields);
        }
    return merged;
    }

很明显如何使用它：

It's pretty obvious how to use it:

DataTable dt1 = new DataTable();
dt1.Columns.Add("ID", typeof(int));
dt1.Columns.Add("Name", typeof(string));
dt1.Rows.Add(1, "Mike");
dt1.Rows.Add(2, "Susan");
var dt2 = new DataTable();
dt2.Columns.Add("Country", typeof(string));
dt2.Rows.Add("UK");
dt2.Rows.Add("France");

DataTable dtMerged = MergeTables(dt1, dt2);

这篇关于使用分隔符将excel转换为文本格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！