Arrangemonk
Disciple
for a school project i have to read excel files and save the result into the orginal excel file
i already have the excel reader using oledb:
public class ExcelReader
{
public DataTable Read(string filename, string iSheetName)
{
DataTable result = new DataTable();
OleDbCommand selectCommand = new OleDbCommand("select * from [" + iSheetName + "$]")
{Connection = OpenConnection(filename)};
OleDbDataAdapter adapterForExcelBook = new OleDbDataAdapter {SelectCommand = selectCommand};
adapterForExcelBook.Fill(result);
selectCommand.Connection.Close();
return result;
}
private static OleDbConnection OpenConnection(string filename)
{
OleDbConnection connectionToExcelBook =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename +
";Extended Properties=Excel 8.0;");
connectionToExcelBook.Open();
return connectionToExcelBook;
}
}
the problem is i cannot use oledb for saving back the values because the're emty in the orginal file and that gives me an error.
i tried this method to resave the whole sheet to the orginal file, but html isnt readable by oledb,
so im kinda stuck here:
public class ExcelWriter
{
public void Write(string filename, string iSheetName, DataTable input)
{
System.Web.UI.WebControls.DataGrid grid =
new System.Web.UI.WebControls.DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.DataSource = input;
grid.DataMember = iSheetName;
grid.DataBind();
using (StreamWriter sw = new StreamWriter(filename))
{
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
grid.RenderControl(hw);
hw.Close();
}
}
so im triing using Microsoft.Office.Interop.Excel;
but thats kinda tricky because them functions are weirdly named and only return objects, so i have to cast everything , and i dont know to what i have to cast it
so fare i have this:
public void Write2(string filename, string iSheetName, DataTable input, string col)
{
var excelApp = new Application();
var workbook = excelApp.Workbooks.Open(filename, 2, false, null, null, null, true, null, null, false, false,
null, false, false, null);
var sheet = (Worksheet) workbook.Worksheets.get_Item(iSheetName);
int count = sheet.Rows.Count;
Range range = sheet.get_Range(col + "0", col + count);
foreach (PivotCell cel in range)
{
}
its not finished, does someone know a usable solution for this
(i just need to replace an emty colum by the filled counterpart)
EDIT:
atm im this far:
public int Write(string filename, string iSheetName, DataTable input)
{
Application excelApp = null;
try
{
excelApp = new Application();
excelApp.Visible = true;
Workbook workBook = excelApp.Workbooks.Open(filename,
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);
Worksheet ws = null;
for (int i = 1; i < workBook.Sheets.Count + 1; i++)
{
if (((Worksheet)workBook.Sheets).Name != iSheetName) continue;
ws = (Worksheet)workBook.Sheets;
break;
}
if (ws == null)
return -1;
//logik(im moment sehr dumm)
for (int i = 0; i < input.Rows.Count; i++)
{
for (int j = 0; j < input.Columns.Count; j++)
{
ws.Cells.Dirty();
ws.Cells[i, j] = input.Rows[j];
}
}
//ws.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing);
workBook.Save();
}
catch (Exception)
{
return -1;
}
finally
{
try
{
excelApp.Quit();
}
catch
{
}
}
return 0;
}
it should work but i forgot the typecast for ws.Cells[i, j] because i have to set the value of the cell
but i guess i can figure that out myself
i already have the excel reader using oledb:
public class ExcelReader
{
public DataTable Read(string filename, string iSheetName)
{
DataTable result = new DataTable();
OleDbCommand selectCommand = new OleDbCommand("select * from [" + iSheetName + "$]")
{Connection = OpenConnection(filename)};
OleDbDataAdapter adapterForExcelBook = new OleDbDataAdapter {SelectCommand = selectCommand};
adapterForExcelBook.Fill(result);
selectCommand.Connection.Close();
return result;
}
private static OleDbConnection OpenConnection(string filename)
{
OleDbConnection connectionToExcelBook =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename +
";Extended Properties=Excel 8.0;");
connectionToExcelBook.Open();
return connectionToExcelBook;
}
}
the problem is i cannot use oledb for saving back the values because the're emty in the orginal file and that gives me an error.
i tried this method to resave the whole sheet to the orginal file, but html isnt readable by oledb,
so im kinda stuck here:
public class ExcelWriter
{
public void Write(string filename, string iSheetName, DataTable input)
{
System.Web.UI.WebControls.DataGrid grid =
new System.Web.UI.WebControls.DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.DataSource = input;
grid.DataMember = iSheetName;
grid.DataBind();
using (StreamWriter sw = new StreamWriter(filename))
{
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
grid.RenderControl(hw);
hw.Close();
}
}
so im triing using Microsoft.Office.Interop.Excel;
but thats kinda tricky because them functions are weirdly named and only return objects, so i have to cast everything , and i dont know to what i have to cast it
so fare i have this:
public void Write2(string filename, string iSheetName, DataTable input, string col)
{
var excelApp = new Application();
var workbook = excelApp.Workbooks.Open(filename, 2, false, null, null, null, true, null, null, false, false,
null, false, false, null);
var sheet = (Worksheet) workbook.Worksheets.get_Item(iSheetName);
int count = sheet.Rows.Count;
Range range = sheet.get_Range(col + "0", col + count);
foreach (PivotCell cel in range)
{
}
its not finished, does someone know a usable solution for this
(i just need to replace an emty colum by the filled counterpart)
EDIT:
atm im this far:
public int Write(string filename, string iSheetName, DataTable input)
{
Application excelApp = null;
try
{
excelApp = new Application();
excelApp.Visible = true;
Workbook workBook = excelApp.Workbooks.Open(filename,
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);
Worksheet ws = null;
for (int i = 1; i < workBook.Sheets.Count + 1; i++)
{
if (((Worksheet)workBook.Sheets).Name != iSheetName) continue;
ws = (Worksheet)workBook.Sheets;
break;
}
if (ws == null)
return -1;
//logik(im moment sehr dumm)
for (int i = 0; i < input.Rows.Count; i++)
{
for (int j = 0; j < input.Columns.Count; j++)
{
ws.Cells.Dirty();
ws.Cells[i, j] = input.Rows[j];
}
}
//ws.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing);
workBook.Save();
}
catch (Exception)
{
return -1;
}
finally
{
try
{
excelApp.Quit();
}
catch
{
}
}
return 0;
}
it should work but i forgot the typecast for ws.Cells[i, j] because i have to set the value of the cell
but i guess i can figure that out myself