This project has moved. For the latest updates, please go here.

Cell type to numeric

Aug 22, 2012 at 4:34 PM

I have tried to make my cells numeric but I haven't been able to. My data comes from datagridview. I am out of ideas, so can you help me and tell me how can I set cells except first column and first row to numeric format, so excel would be able to calculate with the numbers.

 

SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Microsoft Excel 2007 XML|*.xlsx";
saveFileDialog1.Title = "Save an Excel File";
saveFileDialog1.ShowDialog();

if (saveFileDialog1.FileName != "")
{
	DataSet ds = new DataSet();
	DataTable dt = new DataTable();
	dt.TableName = "Prodcuts";
	foreach (DataGridViewColumn col in csv_data.Columns)
	{
		dt.Columns.Add(col.HeaderText);
	}
	foreach (DataGridViewRow gridRow in csv_data.Rows)
	{
		if (gridRow.IsNewRow)
			continue;
		DataRow dtRow = dt.NewRow();
		for (int i1 = 0; i1 < csv_data.Columns.Count; i1++)
		{
			dtRow[i1] = (gridRow.Cells[i1].Value == null ? DBNull.Value : gridRow.Cells[i1].Value);
		}
		dt.Rows.Add(dtRow);
	}
	ds.Tables.Add(dt);

	var newFile = new FileInfo(saveFileDialog1.FileName);
	if (newFile.Exists)
	{
		newFile.Delete();
	}

	var table = dt;

	using (var package = new ExcelPackage(newFile))
	{
		ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Packages");

		worksheet.Cells.LoadFromDataTable(table, true);

		package.Save();
	}
}

Coordinator
Aug 31, 2012 at 11:57 AM

EPPlus will use the datatype of the object for the cell values, so make sure you add the datatable colums as numeric if they contains numeric data.

In this case there is no need to use the datatable. Set the cell values directly instead.

Aug 31, 2012 at 12:14 PM

I did it myself already. placed foreach before package.Save() and looped through cells and used try parse decimal.