1. Creating a new File:

using (var doc = new SpreadsheetDocument(@"C:\OpenXmlPackaging.xlsx")) 

2. Add a new sheet

 Worksheet sheet1 = doc.Worksheets.Add("My Sheet");

3. Putting a value in a cell:

 sheet1.Cells[1, 1].Value = "Test";

 sheet1.Cells["A1"].Value = 1;

4. Some formatting samples:

sheet1.Cells["C3"].Style = new Style {
	Borders = new Borders(BorderStyles.Thick),
	Font = new Font {
		Name = "Consolas",
		Size = 10,
		Color = D.Color.Green,
		Style = FontStyles.DoubleUnderline | FontStyles.Bold
	},
	NumberFormat = new NumberFormat("#,##0.0;[Red](#,##0.0)"),
	Alignment = new Alignment {
		HorizontalAlignment = HorizontalAlignment.Right,
		VerticalAlignment = VerticalAlignment.Top,
		WrapText = true,
		Rotation = 45
	}, 
Fill = new Fill { Color = D.Color.Red }
};

The above code sets the cell C3's style to:

  1. Thick borders.
  2. Font name - Consolas, Size - 10pt, Color - Green, Style - Doubled Underline and Bold.
  3. Number format - Up to one decimal place, negatives shown in Red.
  4. Alignment - Horizontal - right, Vertical - top, Wrapped Text and Rotation 45 degrees
  5. Background Color - Red

5. Merging Cells 

sheet1.Cells.CreateRange("B10:D5").MergeCells();

6. Importing Datatable

sheet1.ImportDataTable(ds.Tables[0], "A1"true);

  Parameters:

  1. Data table to import
  2. Cell from where the import starts
  3. Include Column Names - set to true to include column names while exporting.

7. Auto fit columns: 

sheet1.AutoFitColumns();

8. Set Column Width:

sheet1.SetColumnWidth(3, 12);

Parameters:

  1. Column Index - Column number starts from 1
  2. Width
No need to explicitly save/close the excel file. When you come out of the using statement, the file gets automatically saved!

Last edited May 9, 2013 at 8:13 AM by jamdagni86, version 9

Comments

laffy May 2, 2016 at 9:47 AM 
Any way of changing the print direction of a created document?

smrutiranjanp Jul 16, 2015 at 1:55 PM 
Hi ,
I have tried to fill the excel file from a dataset. but it's always writing the value of last table and not other tables on the dataset. below is my code. Could you please help.

sheet1.ImportDataTable(dsEmployeeLogData.Tables[0], "A1" , true);
sheet1.AutoFitColumns();

for (var counter = 1; counter <= dsEmployeeLogData.Tables.Count - 1; counter++)
{
sheet1.ImportDataTable(dsEmployeeLogData.Tables[counter], "A" + (counter + 1), false);
sheet1.AutoFitColumns();
}

jsudds Sep 4, 2014 at 6:32 PM 
Also wondering about freeze panes?

jsudds Sep 4, 2014 at 6:29 PM 
Do you support hidden columns?

jamdagni86 Jan 14, 2014 at 6:14 AM 
Hi Jonvh, you need not explicitly save/close the excel file. You just have to dispose the object. If you are looking to save the document incrementally, it's not there yet!

jonvh Jan 6, 2014 at 4:17 PM 
Is there some way to explicitly save/close an Excel file?

enricoariel Dec 26, 2013 at 11:58 AM 
if I try to add sheet1.Cells[1, 1].Value = "00100";
excel strips outs the leading zeros. I tried to set
sheet1.Cells[1, 1].Style = new OpenXmlPackaging.Style()
{
NumberFormat = new NumberFormat("@")
};
but did not work.
Is there a way to do that?

jamdagni86 Oct 10, 2013 at 10:34 AM 
Hi,
Can you please attach the code snippet for my reference?

lordoasis Sep 22, 2013 at 12:28 PM 
Hi,
Excel files created cannot be opened directly unless excel repairs them. It appears it has to do with the styling. More so, there is this error " .hexadecimal value 0x1B, is an invalid character.
Any assistance?