in my Program I read and save the values from a .CSV file, separate these values in 2 string types with a special Breakpoint of the Values (BREMSTEST) and then I try to write these strings in a pre-existing excel document .xls (in the computer I work, there is only Excel 2003).
I don't know why this problem happens, but it writes one of the strings (ProfFahrt) in the excel, but the other one (BremTest) not. I have searched a lot and couldn't find the reason. It always comes an Error, but it still writes one of the strings and the other not. The error that appear with the Excel Document is:
Error: Cannot implicitly convert type 'string[,]' to 'Excel.Range' Line: Anonymously Hosted DynamicMethods Assembly.
I have already tried to save everything under the same string, but there is also no difference. And I know it saves the correct values either in PorfFahrt as also in BremTest, because I checked that.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Text;
using Microsoft.Office.Core;
using Excel; // = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace OpenVorlage
{
class Program
{
static void Main(string[] args)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
Excel.Range oRng2;
int k = 10;
int l = 11;
var MyFile = new CSVFile(@"C:\Dokumente und Einstellungen\SZEALVES\Desktop\LOADPROFILE.Test10.csv");
for (int row = 0; row < MyFile.Rows.Count; row++)
{
for (int col = 0; col < MyFile.Rows[row].Fields.Count; col++)
{
if (MyFile.Rows[row].Fields[col] == "BREMSTEST")
{
k = row;
l = k + 1;
}
}
}
try
{
//Excel starten
oXL = new Excel.Application();
oXL.Visible = true;
//gewünschte Dokument abrufen
String fileName = "C:/Dokumente und Einstellungen/SZEALVES/Desktop/Vorlage.xls";
oWB = oXL.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
// Create array to multiple values at once.
string[,] ProfFahrt = new string[400, 20];
string[,] BremTest = new string[400, 20];
// Bremstest Werte Schreiben
for (int row = l; row < MyFile.Rows.Count; row++)
{
for (int col = 0; col < MyFile.Rows[row].Fields.Count; col++)
{
if (col == 0)
{
BremTest[row, 0] = MyFile.Rows[row].Fields[col];
}
if (col == 1)
{
BremTest[row, 1] = MyFile.Rows[row].Fields[col];
}
}
}
// Profilfahrt Werte Schreiben
for (int row = 1; row < k; row++)
{
for (int col = 0; col < MyFile.Rows[row].Fields.Count; col++)
{
if (col == 0)
{
ProfFahrt[row, 0] = MyFile.Rows[row].Fields[col];
}
if (col == 1)
{
ProfFahrt[row, 1] = MyFile.Rows[row].Fields[col];
}
}
}
//Fill I5:J406 (Profilfahrt) with an array of values.
oRng = oSheet.get_Range("I5", "J406").Value2 = ProfFahrt;
//Fill L5:M406 (Bremstest) with an array of values.
oRng2 = oSheet.get_Range("L5", "M406").Value2 = BremTest;
//AutoFit Profilfahrt columns I:J.
oRng = oSheet.get_Range("I1", "J1");
oRng.EntireColumn.AutoFit();
//AutoFit Bremstest columns L:M
oRng2 = oSheet.get_Range("L1", "M1");
oRng2.EntireColumn.AutoFit();
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
public sealed class CSVFile
{
private int m_ColumnCount;
private List<CSVRow> m_Rows;
public int ColumnCount { get { return m_ColumnCount; } }
public System.Collections.ObjectModel.ReadOnlyCollection<CSVRow> Rows { get { return m_Rows.AsReadOnly(); } }
public CSVFile(string path)
{
m_Rows = new List<CSVRow>();
int curCharVal = 0;
char curChar;
bool inQuotes = false;
var curField = new StringBuilder();
var curRow = new CSVRow();
try
{
using (var sr = new System.IO.StreamReader(path))
{
curCharVal = sr.Read();
while (curCharVal >= 0)
{
//We can't be sure if the file we've received uses Line Feed (char 10) by itself, or Carriage Return / Line Feed (chars 13 / char 10) to indicate a new line
//what we can be sure of, however, is that we really don't care if there's a 13!
while (curCharVal == 13)
{
curCharVal = sr.Read();
if (curCharVal == -1)
break;
}
//Sanity check, if we ended up with a -1 due to curCharVal == 13 loop...
//Should never happen, but god knows what some people's CSV files look like
if (curCharVal == -1)
{
curRow.Fields.Add(curField.ToString());
curField.Clear();
this.m_Rows.Add(curRow);
break;
}
curChar = (char)curCharVal;
if (inQuotes)
{
//If we're in a quote enclosed field, we need to identify
// if these new quotes are escaped (doubled)
// and if they are, only add a single set of quotes to our
// current field. If they are not escaped, then we are
// no longer in a quote enclosed field
if (curChar == '"')
{
curCharVal = sr.Read();
if (curCharVal >= 0)
{
curChar = (char)curCharVal;
if (curChar != '"')
{
inQuotes = false;
//The new character we just imported (presumably a comma)
// will be handled once we fall through into the next if block below
}
else
{
curField.Append(curChar);
}
}
}
else
{
curField.Append(curChar);
}
}
//This is a separate if statement, rather than an else clause
// because within the if block above, the inQuotes value could be
// set to false, in which case we want to evaluate the logic
// within this code block
if (!inQuotes)
{
if (curField.Length == 0 && curChar == '"')
{
inQuotes = true;
}
else if (curChar == ',')
{
curRow.Fields.Add(curField.ToString());
curField.Clear();
}
else if (curCharVal == 10)
{
curRow.Fields.Add(curField.ToString());
curField.Clear();
//We're done with this row, add it to the list and set
// ourselves up for a fresh row.
this.m_Rows.Add(curRow);
curRow = new CSVRow();
}
else
{
curField.Append(curChar);
}
}
curCharVal = sr.Read();
//We just reached the end of the file.
// Add the current row to the list of rows before the loop ends
if (curCharVal == -1)
{
curRow.Fields.Add(curField.ToString());
curField.Clear();
}
}
}
}
catch
{
m_Rows.Clear();
m_ColumnCount = 0;
}
}
public sealed class CSVRow
{
private List<string> m_Fields;
public List<string> Fields { get { return m_Fields; } }
public CSVRow()
{
m_Fields = new List<string>();
}
}
}
}
in my Program I read and save the values from a .CSV file, separate these values in 2 string types with a special Breakpoint of the Values (BREMSTEST) and then I try to write these strings in a pre-existing excel document .xls (in the computer I work, there is only Excel 2003).
I don't know why this problem happens, but it writes one of the strings (ProfFahrt) in the excel, but the other one (BremTest) not. I have searched a lot and couldn't find the reason. It always comes an Error, but it still writes one of the strings and the other not. The error that appear with the Excel Document is:
Error: Cannot implicitly convert type 'string[,]' to 'Excel.Range' Line: Anonymously Hosted DynamicMethods Assembly.
I have already tried to save everything under the same string, but there is also no difference. And I know it saves the correct values either in PorfFahrt as also in BremTest, because I checked that.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Text;
using Microsoft.Office.Core;
using Excel; // = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace OpenVorlage
{
class Program
{
static void Main(string[] args)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
Excel.Range oRng2;
int k = 10;
int l = 11;
var MyFile = new CSVFile(@"C:\Dokumente und Einstellungen\SZEALVES\Desktop\LOADPROFILE.Test10.csv");
for (int row = 0; row < MyFile.Rows.Count; row++)
{
for (int col = 0; col < MyFile.Rows[row].Fields.Count; col++)
{
if (MyFile.Rows[row].Fields[col] == "BREMSTEST")
{
k = row;
l = k + 1;
}
}
}
try
{
//Excel starten
oXL = new Excel.Application();
oXL.Visible = true;
//gewünschte Dokument abrufen
String fileName = "C:/Dokumente und Einstellungen/SZEALVES/Desktop/Vorlage.xls";
oWB = oXL.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
// Create array to multiple values at once.
string[,] ProfFahrt = new string[400, 20];
string[,] BremTest = new string[400, 20];
// Bremstest Werte Schreiben
for (int row = l; row < MyFile.Rows.Count; row++)
{
for (int col = 0; col < MyFile.Rows[row].Fields.Count; col++)
{
if (col == 0)
{
BremTest[row, 0] = MyFile.Rows[row].Fields[col];
}
if (col == 1)
{
BremTest[row, 1] = MyFile.Rows[row].Fields[col];
}
}
}
// Profilfahrt Werte Schreiben
for (int row = 1; row < k; row++)
{
for (int col = 0; col < MyFile.Rows[row].Fields.Count; col++)
{
if (col == 0)
{
ProfFahrt[row, 0] = MyFile.Rows[row].Fields[col];
}
if (col == 1)
{
ProfFahrt[row, 1] = MyFile.Rows[row].Fields[col];
}
}
}
//Fill I5:J406 (Profilfahrt) with an array of values.
oRng = oSheet.get_Range("I5", "J406").Value2 = ProfFahrt;
//Fill L5:M406 (Bremstest) with an array of values.
oRng2 = oSheet.get_Range("L5", "M406").Value2 = BremTest;
//AutoFit Profilfahrt columns I:J.
oRng = oSheet.get_Range("I1", "J1");
oRng.EntireColumn.AutoFit();
//AutoFit Bremstest columns L:M
oRng2 = oSheet.get_Range("L1", "M1");
oRng2.EntireColumn.AutoFit();
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
}
public sealed class CSVFile
{
private int m_ColumnCount;
private List<CSVRow> m_Rows;
public int ColumnCount { get { return m_ColumnCount; } }
public System.Collections.ObjectModel.ReadOnlyCollection<CSVRow> Rows { get { return m_Rows.AsReadOnly(); } }
public CSVFile(string path)
{
m_Rows = new List<CSVRow>();
int curCharVal = 0;
char curChar;
bool inQuotes = false;
var curField = new StringBuilder();
var curRow = new CSVRow();
try
{
using (var sr = new System.IO.StreamReader(path))
{
curCharVal = sr.Read();
while (curCharVal >= 0)
{
//We can't be sure if the file we've received uses Line Feed (char 10) by itself, or Carriage Return / Line Feed (chars 13 / char 10) to indicate a new line
//what we can be sure of, however, is that we really don't care if there's a 13!
while (curCharVal == 13)
{
curCharVal = sr.Read();
if (curCharVal == -1)
break;
}
//Sanity check, if we ended up with a -1 due to curCharVal == 13 loop...
//Should never happen, but god knows what some people's CSV files look like
if (curCharVal == -1)
{
curRow.Fields.Add(curField.ToString());
curField.Clear();
this.m_Rows.Add(curRow);
break;
}
curChar = (char)curCharVal;
if (inQuotes)
{
//If we're in a quote enclosed field, we need to identify
// if these new quotes are escaped (doubled)
// and if they are, only add a single set of quotes to our
// current field. If they are not escaped, then we are
// no longer in a quote enclosed field
if (curChar == '"')
{
curCharVal = sr.Read();
if (curCharVal >= 0)
{
curChar = (char)curCharVal;
if (curChar != '"')
{
inQuotes = false;
//The new character we just imported (presumably a comma)
// will be handled once we fall through into the next if block below
}
else
{
curField.Append(curChar);
}
}
}
else
{
curField.Append(curChar);
}
}
//This is a separate if statement, rather than an else clause
// because within the if block above, the inQuotes value could be
// set to false, in which case we want to evaluate the logic
// within this code block
if (!inQuotes)
{
if (curField.Length == 0 && curChar == '"')
{
inQuotes = true;
}
else if (curChar == ',')
{
curRow.Fields.Add(curField.ToString());
curField.Clear();
}
else if (curCharVal == 10)
{
curRow.Fields.Add(curField.ToString());
curField.Clear();
//We're done with this row, add it to the list and set
// ourselves up for a fresh row.
this.m_Rows.Add(curRow);
curRow = new CSVRow();
}
else
{
curField.Append(curChar);
}
}
curCharVal = sr.Read();
//We just reached the end of the file.
// Add the current row to the list of rows before the loop ends
if (curCharVal == -1)
{
curRow.Fields.Add(curField.ToString());
curField.Clear();
}
}
}
}
catch
{
m_Rows.Clear();
m_ColumnCount = 0;
}
}
public sealed class CSVRow
{
private List<string> m_Fields;
public List<string> Fields { get { return m_Fields; } }
public CSVRow()
{
m_Fields = new List<string>();
}
}
}
}
0 commentaires:
Enregistrer un commentaire