excel定界不定期 [英] excel delimiting not working on a regular basis

问题描述

大家好，

我有一个以"|"分隔的Excel工作簿我正在使用以下代码使用以下代码读取文件，有朝一日，它可以正常工作，但现在不行.我正在使用excel interop 12.0，我的文件是xlsx格式

请帮忙

Hi All,

I have an excel workbook delimited by "|" and i am using the following code to read the file using the following code, somedays back it was working fine but not now. I am using excel interop 12.0 and my file is of xlsx format

Please help

private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
            {
                MessageBox.Show("Folder not selected", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            else
            {
                try
                {
                    string path = textBox1.Text;
                    string Filename = path.Substring(path.LastIndexOf(''\\'') + 1);
                    using (SqlConnection Connection = ConnectionManager.GetConnection())
                    {
                        Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
                        Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(Filename, 0, true, 6, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "|", true, false, 0, true, false, false);
                        Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;
                        Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
                        int rowIndex = 1;
                        int colIndex1 = 1;
                    //int index = 0;
                    a: while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 != null)
                        {
                            //int cindex = 0;
                            tblColumns.Clear();
                            while (colIndex1 < 7)
                            {
                                //colIndex1 = colIndex1 + cindex;
                                if (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2 == null)
                                {
                                    tblColumns.Add("");
                                }
                                else
                                {
                                    string val1 = ((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, colIndex1]).Value2.ToString();
                                    tblColumns.Add(val1);
                                }
                                colIndex1++;
                            }
                            string query = "";
                            query = "insert into singlebarcode values (";
                            foreach (string line in tblColumns)
                            {
                                query = query + "''" + line + "'',";
                            }
                            string query1 = query.Substring(0, query.Length - 1);
                            query2 = query1 + ")";
                            SqlCommand command = new SqlCommand(query2, Connection);
                            command.CommandType = CommandType.Text;
                            command.ExecuteNonQuery();
                            count++;
                            rowIndex++;
                            colIndex1 = 1;
                            goto a;
                        }
                        listBox1.Items.Add(count + " rows inserted");
                        ExcelObj.Workbooks.Close();
                        ExcelObj.Quit();
                    }
                    
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    ExcelObj.Quit();
                }
                
            }
        }

[edit]忽略HTML ..."选项已禁用，代码块已整理-OriignalGriff [/edit]

[edit]"ignore HTML..." option disabled, code blocks tidied up - OriignalGriff[/edit]

推荐答案

两件事:
1)SQL语法.
您的插入语句为:

Two things:
1) SQL syntax.
Your insert statement is:

string query = "";
query = "insert into singlebarcode values (";
foreach (string line in tblColumns)
{
    query = query + "''" + line + "'',";
}
string query1 = query.Substring(0, query.Length - 1);
query2 = query1 + ")";

相当于:

string query2 = "INSERT INTO singlebarcode VALUES (''from excel'')";

插入命令的SQL语法需要列列表:

SQL syntax for an insert command requires the columns list:

string query2 = "INSERT INTO singlebarcode (myColumnName) VALUES (''from excel'')";

2)还是不要那样做！您对所谓的SQL注入攻击大开眼界(Google"Bobby Tables"了解我的意思).请改用参数化查询:

2) Do not do it that way anyway! You leave yourself wide open for what is called an SQL Injection attack (Google "Bobby Tables" for find out what I mean). Use parametrized queries instead:

string query = "INSERT INTO singlebarcode (myColumn1, myColumn2) VALUES (@MC1, @MC2)";
SqlCommand command = new SqlCommand(query, Connection);
int i = 1;
foreach (string line in tblColumns)
{
    command.Parameters.AddWithValue("@MC" + i.ToString(), line);
    i++;
}

我怀疑您需要将Excel文件中的每一行插入数据库中的另一行，但是在看不到数据库布局的情况下，我无法分辨.

I suspect that you need to insert each line from the Excel file into a different row in you DB, but without seeing your DB layout, I can''t tell.

什么难道您正在尝试赶上吗?

在这行代码中.您必须对变量"line"中的字符串值进行转义.否则，当变量"line"的值为"这一切都是关于爱情"时，您将得到一条无效的SQL语句.

What exception are you getting in the try catch ?

In this line of code. You have to escape the string value in variable "line". Else when variable "line" haves a value of "It''s all about love" you will end up with a SQL statement that is invalid.

foreach (string line in tblColumns)
{
  query = query + "'" + line + "',";
}

生成SQL语句的最佳方法是使用StringBuilder和SqlParamenter.

The best way to build your SQL statement is using a StringBuilder and SqlParamenter.

这篇关于excel定界不定期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！