C# 替换或更新 Excel 文件中的条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15899046/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Replace or update entries in Excel file
提问by Robert
I'm trying to cleanse some data ('phone numbers in this example) in an Excel (.xlsx) file on upload. I have the following code to open the file:
我正在尝试在上传时清理 Excel (.xlsx) 文件中的一些数据（本例中为“电话号码”）。我有以下代码来打开文件：
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
try
{
string filePath = ("confirm//") + FileUpload1.FileName;
FileUpload1.SaveAs(Server.MapPath(filePath));
Microsoft.Office.Interop.Excel.Application xl =
new Microsoft.Office.Interop.Excel.Application();
Workbook wb =
xl.Application.Workbooks.Open(Server.MapPath(filePath));
wb.Activate();
string csvPath = (filePath.Replace(".xlsx", ".csv"));
wb.SaveAs(Server.MapPath(csvPath),
Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV);
wb.Close();
// call method to parse csv
ReadRec(csvPath);
}
catch (Exception ex)
{//}
else
{//}
}
Then something like this to add a zero to the start of the number if not already there:
然后像这样在数字的开头添加一个零（如果还没有）：
private void ReadRec(string csvName)
{
StreamReader Sr = new StreamReader(Server.MapPath(csvName));
string s;
while (!Sr.EndOfStream)
{
s = Sr.ReadLine();
string company = s.Split(',')[0];
string phone = s.Split(',')[1];
string NAME = s.Split(',')[2];
if (!phone.StartsWith("0"))
{
phone = "0" + phone;
}
}
Sr.Close();
}
This seems to work well but what I haven't been able to figure out is how to re-insert the updated numbers back into the spreadsheet (or create a new Excel file with the updated data).
这似乎运行良好，但我无法弄清楚如何将更新的数字重新插入电子表格（或使用更新的数据创建新的 Excel 文件）。
Can anyone give me some pointers?
谁能给我一些指点？
采纳答案by Robert
OK, finally cobbled together a solution that sort of works (well enough for my current purposes) but is far from ideal:
好的，最后拼凑出一个可行的解决方案（对于我目前的目的来说已经足够了），但远非理想：
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
try
{
var excelApp = new Application();
excelApp.Workbooks.Open("C:\myFile.xls", Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
var ws = excelApp.Worksheets;
var worksheet = (Worksheet)ws.get_Item("Sheet1");
Range range = worksheet.UsedRange;
object[,] values = (object[,])range.Value2;
for (int row = 1; row <= values.GetUpperBound(0); row++)
{
string phone = Convert.ToString(values[row, 2]);
if (!phone.StartsWith("0"))
{
phone = "0" + phone;
}
range.Cells.set_Item(row, 2, phone);
}
excelApp.Save("C:\Leads.xls");
excelApp.Quit();
}
catch (Exception ex)
{//}
else
{//}
}
-EDIT- In order for this to work, I had to open the .xlsxfile in Excel and save it as .xls.
- 编辑 - 为了使其工作，我必须.xlsx在 Excel 中打开文件并将其另存为.xls.
回答by NickSlash
I don't really use c#or asp.netso this (probably) wont be working code.
我并没有真正使用c#或asp.net，所以这（可能）不会是工作代码。
Interop in C# shouldbe really simple, the documentation usually has examples specific to C# so if this fails, take a look at the MSDN.
C# 中的互操作应该非常简单，文档通常包含特定于 C# 的示例，因此如果失败，请查看 MSDN。
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
try
{
string filePath = ("confirm//") + FileUpload1.FileName;
FileUpload1.SaveAs(Server.MapPath(filePath));
Microsoft.Office.Interop.Excel.Application xl =
new Microsoft.Office.Interop.Excel.Application();
Workbook wb =
xl.Application.Workbooks.Open(Server.MapPath(filePath));
wb.Activate();
Worksheet ws = wb.Worksheets(1); // use the first sheet 1-based index i think
for (long row = 2; i <= ws.UsedRange.Rows.Count; i++) {
// assume row 1 is a title so start at row 2
// ws.Cells(row, 1) // company
// ws.Cells(row, 2) // phone
// ws.Cells(row, 3) // name
if (!ws.Cells(row,2).Value2.StartsWith("0") {
ws.Cells(row,2).Value2 = "0" + ws.Cells(row,2).Value2
}
}
//string csvPath = (filePath.Replace(".xlsx", ".csv"));
//wb.SaveAs(Server.MapPath(csvPath),
// Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV);
wb.Save(); // as were directly modifying the file, no need to save elsewhere
wb.Close();
// call method to parse csv
//ReadRec(csvPath);
}
catch (Exception ex)
{//}
else
{//}
}
回答by Ismayil S
I have using this code for UPDATE the data in the EXCEL sheet
我已使用此代码更新 EXCEL 表中的数据
try
{
cmd.CommandText = "UPDATE [Sheet1$] SET ID=@value,Name=@value2,Age=@value3 where ID=@value4";
cn.Open();
cmd.Parameters.AddWithValue("@value1", txtid.Text);
cmd.Parameters.AddWithValue("@value2", txtname.Text);
cmd.Parameters.AddWithValue("@value3", txtage.Text);
cmd.Parameters.AddWithValue("@value4", txtupdate.Text);
cmd.ExecuteNonQuery();
MessageBox.Show("Record Updated Successfully");
cn.Close();
}
catch (Exception e3)
{
MessageBox.Show("Error" + e3);
}
