private static void CreateSpreadSheet()
{
string fileName = "E:\\01.xlsx";
string sheetName = "test";
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create the workbook
spreadSheet.AddWorkbookPart();
spreadSheet.WorkbookPart.Workbook = new Workbook(); // create the worksheet
spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
SharedStringTablePart m_SharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
// create sheet data
SheetData m_SheetData = new SheetData();
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(m_SheetData);
// create row
//spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
create cell with data
//spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(new Cell() { CellValue = new CellValue("100") });
//Add Data
Row row1 = new Row() { RowIndex = 1 };
Cell cell1 = new Cell() { CellReference = "A1" };
int index = InsertSharedStringItem("Sun", m_SharedStringTablePart);
cell1.CellValue = new CellValue(index.ToString());
cell1.DataType = new EnumValue<CellValues>(CellValues.SharedString);
row1.Append(cell1);
m_SheetData.Append(row1);
// save worksheet
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();
// create the worksheet to workbook relation
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = sheetName
});
spreadSheet.WorkbookPart.Workbook.Save();
}
}
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
shareStringPart.SharedStringTable.Count = 1;
shareStringPart.SharedStringTable.UniqueCount = 1;
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
{
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
{
string fileName = "E:\\01.xlsx";
string sheetName = "test";
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create the workbook
spreadSheet.AddWorkbookPart();
spreadSheet.WorkbookPart.Workbook = new Workbook(); // create the worksheet
spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
SharedStringTablePart m_SharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
// create sheet data
SheetData m_SheetData = new SheetData();
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(m_SheetData);
// create row
//spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
create cell with data
//spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(new Cell() { CellValue = new CellValue("100") });
//Add Data
Row row1 = new Row() { RowIndex = 1 };
Cell cell1 = new Cell() { CellReference = "A1" };
int index = InsertSharedStringItem("Sun", m_SharedStringTablePart);
cell1.CellValue = new CellValue(index.ToString());
cell1.DataType = new EnumValue<CellValues>(CellValues.SharedString);
row1.Append(cell1);
m_SheetData.Append(row1);
// save worksheet
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();
// create the worksheet to workbook relation
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = sheetName
});
spreadSheet.WorkbookPart.Workbook.Save();
}
}
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
shareStringPart.SharedStringTable.Count = 1;
shareStringPart.SharedStringTable.UniqueCount = 1;
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
{
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}