如何在C#中安全地从视图类型插入到excel工作表

Question

我打算从视图中插入一些数据到excel工作表中，但是带有decimal值的列没有插入数字类型，它是作为文本.How插入的吗？

here is an example

 using (OleDbConnection cn = new OleDbConnection(connectionString))
            {
                cn.Open();


                for (int i = 0; i < Grid.RowCount; i++)
                {
                    OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [Data$] " +
                     "([Title],[Name],[DayOfWeek],[Approval State],[Date],[User ID],[Week],[Project Code],[Project Regular Hours],[Project Overtime Hours],[Sick],[Vacation],[Holiday],[Unpaid Leave],[Other],[Timesheet URL])" +
                     "VALUES(@Title,@Name,@DayOfWeek,@ApprovalState,@Date,@UserID,@Week,@ProjectCode,@ProjectRegularHours,@ProjectOvertimeHours,@Sick,@Vacation,@Holiday,@UnpaidLeave,@Other,@TimesheetURL)", cn);



                    List<OleDbParameter> parameters = new List<OleDbParameter>
                    {
                        new OleDbParameter("@Title", Grid.GetRowCellValue(i,"Title").ToString()),
                        new OleDbParameter("@Name", Grid.GetRowCellValue(i,"Ad_Soyad").ToString()),
                        new OleDbParameter("@DayOfWeek", Grid.GetRowCellValue(i,"DayOfWeeks").ToString()),
                        new OleDbParameter("@ApprovalState", Grid.GetRowCellValue(i,"ApprovalState").ToString()),
                        new OleDbParameter("@Date",Grid.GetRowCellValue(i,"Tarix").ToString()),
                        new OleDbParameter("@UserID", Grid.GetRowCellValue(i,"UserID").ToString()),
                        new OleDbParameter("@Week", Grid.GetRowCellValue(i,"Weeks").ToString()),
                        new OleDbParameter("@ProjectCode", Grid.GetRowCellValue(i, "ProjectCode").ToString()),
                        new OleDbParameter("@ProjectRegularHours",Convert.ToDecimal(Grid.GetRowCellValue(i, "ProjectRegularHours").ToString(),new CultureInfo("en"))){DbType=DbType.Decimal},
                        new OleDbParameter("@ProjectOvertimeHours", Grid.GetRowCellValue(i,"ProjectOvertimeHours").ToString()){DbType=DbType.Decimal},
                        new OleDbParameter("@Sick", Grid.GetRowCellValue(i,"Sick").ToString()),
                        new OleDbParameter("@Vacation", Grid.GetRowCellValue(i,"Vacation").ToString()),
                        new OleDbParameter("@Holiday", Grid.GetRowCellValue(i,"Holiday").ToString()),
                        new OleDbParameter("@UnpaidLeave", Grid.GetRowCellValue(i,"UnpaidLeave").ToString()),
                        new OleDbParameter("@Other", Grid.GetRowCellValue(i,"Other").ToString()),
                        new OleDbParameter("@TimesheetURL", Grid.GetRowCellValue(i,"TimesheetURL").ToString())
                    };

                    cmd1.Parameters.AddRange(parameters.ToArray());

                    cmd1.ExecuteNonQuery();
                }


            }

Answer 1

代码itsels在将值插入Excel之前将其转换为本地化字符串。这肯定会导致转换问题。如果您在任何欧洲国家运行此代码，24.5将变为24,5。

不要将值转换为字符串。如果它们是作为对象返回的，请先将它们强制转换为正确的类型。您也不需要在每次迭代中重新构建命令对象。只需创建一次，传递正确的OleDbType并更改每行的参数，例如：

var query="INSERT INTO [Data$] " +
                 "([Title],[Name],[DayOfWeek],[Approval State],[Date],[User ID],[Week],[Project Code],[Project Regular Hours],[Project Overtime Hours],[Sick],[Vacation],[Holiday],[Unpaid Leave],[Other],[Timesheet URL])" +
                 "VALUES(@Title,@Name,@DayOfWeek,@ApprovalState,@Date,@UserID,@Week,@ProjectCode,@ProjectRegularHours,@ProjectOvertimeHours,@Sick,@Vacation,@Holiday,@UnpaidLeave,@Other,@TimesheetURL)";
var cmd1 = new OleDbCommand(query, cn);

cmd1.Parameters.Add("@Title",OleDbType.VarWChar,20 );
cmd1.Parameters.Add("@Name",OleDbType.VarWChar,20 );
cmd1.Parameters.Add("@DayOfWeek",OleDbType.VarWChar,20 );
...      

cmd1.Parameters.Add("@ProjectRegularHours",OleDbType.Decimal,19,4);

for (int i = 0; i < Grid.RowCount; i++)
{
    cmd1.Parameters["@Title"].Value = Grid.GetRowCellValue(i,"Title");
    ...
    cmd1.Parameters["@ProjectRegularHours"].Value = (decimal)Grid.GetRowCellValue(i,"ProjectRegularHours"))
    cmd1.ExecuteNonQuery();
}

还有更好的选择。如果网格使用数据绑定绑定到DataTable，则可以使用OleDbTableAdapter一次编写整个表。

var range=sheet.LoadFromDataTable(myTable,true, TableStyles.Dark1);

或

var range=sheet.LoadFromCollection(myListOfCustomers);

您还可以将结果范围转换为命名表：

using (var xlPackage = new ExcelPackage(new FileInfo(somePath))
{
    // add a sheet
    var ws = xlPackage.Workbook.Worksheets.Add("Sheet1");
    var table = ws.Tables.Add(range, "table1");
    table.ShowTotal = true;
    table.TableStyle = TableStyles.Light2;
    ...
}