إدراج نص في خلية

OpenXML Excel

 string FilePath = @"..\..\..\..\Sample Files\";

string FileName = FilePath + "Insert text into a cell.xlsx";

InsertText(FileName, "Inserted Text");

// Given a document name and text, 

// inserts a new work sheet and writes the text to cell "A1" of the new worksheet.

public static void InsertText(string docName, string text)

{

    // Open the document for editing.

    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))

    {

        // Get the SharedStringTablePart. If it does not exist, create a new one.

        SharedStringTablePart shareStringPart;

        if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)

        {

            shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();

        }

        else

        {

            shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();

        }

        // Insert the text into the SharedStringTablePart.

        int index = InsertSharedStringItem(text, shareStringPart);

        // Insert a new worksheet.

        WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

        // Insert cell A1 into the new worksheet.

        Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

        // Set the value of cell A1.

        cell.CellValue = new CellValue(index.ToString());

        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

        // Save the new worksheet.

        worksheetPart.Worksheet.Save();

    }

}

// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 

// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.

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();

    }

    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;

}

// Given a WorkbookPart, inserts a new worksheet.

private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)

{

    // Add a new worksheet part to the workbook.

    WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();

    newWorksheetPart.Worksheet = new Worksheet(new SheetData());

    newWorksheetPart.Worksheet.Save();

    Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();

    string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

    // Get a unique ID for the new sheet.

    uint sheetId = 1;

    if (sheets.Elements<Sheet>().Count() > 0)

    {

        sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;

    }

    string sheetName = "Sheet" + sheetId;

    // Append the new worksheet and associate it with the workbook.

    Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };

    sheets.Append(sheet);

    workbookPart.Workbook.Save();

    return newWorksheetPart;

}

// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 

// If the cell already exists, returns it. 

private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)

{

    Worksheet worksheet = worksheetPart.Worksheet;

    SheetData sheetData = worksheet.GetFirstChild<SheetData>();

    string cellReference = columnName + rowIndex;

    // If the worksheet does not contain a row with the specified row index, insert one.

    Row row;

    if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)

    {

        row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();

    }

    else

    {

        row = new Row() { RowIndex = rowIndex };

        sheetData.Append(row);

    }

    // If there is not a cell with the specified column name, insert one.  

    if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)

    {

        return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();

    }

    else

    {

        // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.

        Cell refCell = null;

        foreach (Cell cell in row.Elements<Cell>())

        {

            if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)

            {

                refCell = cell;

                break;

            }

        }

        Cell newCell = new Cell() { CellReference = cellReference };

        row.InsertBefore(newCell, refCell);

        worksheet.Save();

        return newCell;

    }

}

Aspose.Cells

 string FilePath = @"..\..\..\..\Sample Files\";

string FileName = FilePath + "Insert text into a cell.xlsx";

InsertText(FileName, "Inserted Text");

private static void InsertText(string docName, string text)

{

    //Instantiating a Workbook object

    Workbook workbook = new Workbook(docName);

    //Obtaining the reference of the Active worksheet

    Worksheet worksheet = workbook.Worksheets[workbook.Worksheets.ActiveSheetIndex];

    //insert value from cell

    worksheet.Cells["A1"].PutValue(text);

    //Saving the Excel file

    workbook.Save(docName);

}

تحميل رمز عينة

• GitHub
• Sourceforge
• Bitbucket