将数据从一个excel文件传输到另一个
问题描述：
我有一个会计程序，它生成报告并将它们保存在excel文件中。但我不需要这份报告中的大部分专栏。所以我创建了一个程序来删除我不需要的列。在转换后的文件中，我使用3个空列来编写笔记，数据等。我每周都会生成这个报告，因此当生成新报告时，我需要将其转换并将旧报告中写入的3列转移到新的那一个。报告的第一列是UserID，因此我需要传输为ID编写的笔记和数据。新报告中的某些ID可能会丢失。 我创建了一个应该这样做的Windows窗体应用程序，但它不起作用。新报告将被转换，但旧数据不会被转移。有任何想法吗？？这是我的代码：将数据从一个excel文件传输到另一个
OpenExcelWorkbook(OldFilePath);
_sheet = (Excel.Worksheet)_sheets[1];
_sheet.Select(Type.Missing);
var oldData = new DataTable();
oldData.Columns.Add("Id", typeof(string));
oldData.Columns.Add("Comment1", typeof(string));
oldData.Columns.Add("Comment2", typeof(string));
oldData.Columns.Add("Comment3", typeof(string));
var range = _sheet.UsedRange;
for (var i = 1; i <= range.Rows.Count; i++)
{
oldData.Rows.Add(_sheet.Range["A" + i, Type.Missing].Value2, _sheet.Range["J" + i, Type.Missing].Value2, _sheet.Range["K" + i, Type.Missing].Value2,
_sheet.Range["L" + i, Type.Missing].Value2);
}
_book.Save();
_book.Close(false, Type.Missing, Type.Missing);
_app.Quit();
releaseObject(_sheet);
releaseObject(_book);
releaseObject(_app);
OpenExcelWorkbook(NewFilePath);
_sheet = (Excel.Worksheet)_sheets[1];
_sheet.Select(Type.Missing);
range = _sheet.Range["D:E", Type.Missing];
range.EntireColumn.Delete(Type.Missing);
range = _sheet.Range["E:F", Type.Missing];
range.EntireColumn.Delete(Type.Missing);
range = _sheet.Range["F:I", Type.Missing];
range.EntireColumn.Delete(Type.Missing);
_sheet.Cells[4, 3] = "Invoice";
_sheet.Cells[4, 4] = "Transfer";
_sheet.Cells[4, 5] = "Receipt";
range = _sheet.UsedRange;
var convDataTable = new DataTable();
convDataTable.Columns.Add("Id", typeof(string));
convDataTable.Columns.Add("Comment1", typeof(string));
convDataTable.Columns.Add("Comment2", typeof(string));
convDataTable.Columns.Add("Comment3", typeof(string));
for (var i = 1; i < range.Rows.Count; i++)
convDataTable.Rows.Add(_sheet.Range["A" + i, Type.Missing].Value2, "", "", "");
foreach (DataRow row in convDataTable.Rows)
{
foreach (DataRow row1 in oldData.Rows)
{
if (row["Id"] == row1["Id"])
{
row["Comment1"] = row1["Comment1"];
row["Comment2"] = row1["Comment2"];
row["Comment3"] = row1["Comment3"];
}
}
}
for (var i = 1; i <= convDataTable.Rows.Count; i++)
{
_sheet.Cells[i, 10] = convDataTable.Rows[i - 1]["Comment1"];
_sheet.Cells[i, 11] = convDataTable.Rows[i - 1]["Comment2"];
_sheet.Cells[i, 12] = convDataTable.Rows[i - 1]["Comment3"];
}
_book.Save();
_book.Close(false, Type.Missing, Type.Missing);
_app.Quit();
releaseObject(_sheet);
releaseObject(_book);
releaseObject(_app);
这些是我使用的功能。
private static void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
protected void OpenExcelWorkbook(string filePath)
{
_app = new Application();
_books = _app.Workbooks;
_book = _books.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_sheets = _book.Worksheets;
}
答
我解决了这个问题。这是解决方案：
OpenExcelWorkbook(OldFilePath);
_sheet = (Excel.Worksheet)_sheets[1];
_sheet.Select(Type.Missing);
var oldData = new DataTable();
oldData.Columns.Add("Id", typeof(string));
oldData.Columns.Add("Comment1", typeof(string));
oldData.Columns.Add("Comment2", typeof(string));
oldData.Columns.Add("Comment3", typeof(string));
var range = _sheet.UsedRange;
for (var i = 1; i <= range.Rows.Count; i++)
{
var id = "";
var comment1 = "";
var comment2 = "";
var comment3 = "";
if (((Excel.Range)_sheet.Cells[i, 1]).Value2 != null)
id = ((Excel.Range)_sheet.Cells[i, 1]).Value2.ToString();
if (((Excel.Range)_sheet.Cells[i, 10]).Value2 != null)
comment1 = ((Excel.Range)_sheet.Cells[i, 10]).Value2.ToString();
if (((Excel.Range)_sheet.Cells[i, 11]).Value2 != null)
comment2 = ((Excel.Range)_sheet.Cells[i, 11]).Value2.ToString();
if (((Excel.Range)_sheet.Cells[i, 12]).Value2 != null)
comment3 = ((Excel.Range)_sheet.Cells[i, 12]).Value2.ToString();
if (comment1 != "" || comment2 != "" || comment3 != "")
oldData.Rows.Add(id, comment1, comment2, comment3);
}
_book.Save();
_book.Close(false, Type.Missing, Type.Missing);
_app.Quit();
releaseObject(_sheet);
releaseObject(_book);
releaseObject(_app);
OpenExcelWorkbook(NewFilePath);
_sheet = (Excel.Worksheet)_sheets[1];
_sheet.Select(Type.Missing);
range = _sheet.Range["D:E", Type.Missing];
range.EntireColumn.Delete(Type.Missing);
range = _sheet.Range["E:F", Type.Missing];
range.EntireColumn.Delete(Type.Missing);
range = _sheet.Range["F:I", Type.Missing];
range.EntireColumn.Delete(Type.Missing);
_sheet.Cells[4, 3] = "Invoice";
_sheet.Cells[4, 4] = "Transaction";
_sheet.Cells[4, 5] = "Receipt";
range = _sheet.UsedRange;
var convDataTable = new DataTable();
convDataTable.Columns.Add("Id", typeof(string));
convDataTable.Columns.Add("Comment1", typeof(string));
convDataTable.Columns.Add("Comment2", typeof(string));
convDataTable.Columns.Add("Comment3", typeof(string));
for (var i = 1; i < range.Rows.Count; i++)
{
var id = "";
if (((Excel.Range)_sheet.Cells[i, 1]).Value2 != null)
id = ((Excel.Range)_sheet.Cells[i, 1]).Value2.ToString();
convDataTable.Rows.Add(id, "", "", "");
}
foreach (DataRow row in convDataTable.Rows)
{
foreach (DataRow row1 in oldData.Rows)
{
var oldId = row1[0].ToString();
var newId = row[0].ToString();
if (newId != "")
{
if (newId == oldId)
{
var comment1 = row1[1].ToString();
var comment2 = row1[2].ToString();
var comment3 = row1[3].ToString();
row[1] = comment1;
row[2] = comment2;
row[3] = comment3;
break;
}
}
}
}
for (var i = 1; i <= convDataTable.Rows.Count; i++)
{
_app.Cells[i, 10] = convDataTable.Rows[i - 1]["Comment1"].ToString();
_app.Cells[i, 11] = convDataTable.Rows[i - 1]["Comment2"].ToString();
_app.Cells[i, 12] = convDataTable.Rows[i - 1]["Comment3"].ToString();
}
_book.Save();
_book.Close(false, Type.Missing, Type.Missing);
_app.Quit();
releaseObject(_sheet);
releaseObject(_book);
releaseObject(_app);
MessageBox.Show("The converting is finished");
您是否试过单步执行代码？评论信息是否将其转换为旧数据？当您到达底部的循环并且您试图将其复制到新的电子表格中时，它仍然存在吗？ – forsvarir 2011-03-24 10:28:28