private static bool CreateOrUpdateRowAt(SheetData sheetData, Row newRow, uint rowNo)
{
bool res = false;
//create row and cell. append or insert.
if (sheetData != null && newRow != null)
{
//是否存在row,1.存在,删除.2.现在是否存在大于row,存在,在前插入.不存在.直接附加.
var rows = sheetData.Elements <Row>().Where(x => x.RowIndex == rowNo);
if (rows.Count() > 0)
{
sheetData.RemoveChild(rows.First());
}
var biggerrows = sheetData.Elements <Row>().Where(x => x.RowIndex > rowNo);
if (biggerrows.Count() <= 0)
{
sheetData.Append(newRow);//todo:装载数据7/10的时间耗费在这里。需要优化！如果是大数据插入，应该创建大量row，再使用一次append或其他插入函数。
}
else
{
sheetData.InsertBefore(newRow, biggerrows.First());
}
res = true;
}
return(res);
}
public static void RemoveRow(WorkbookPart workbookPart, string sheetName, uint rowIndex)
{
WorksheetPart worksheetPart = workbookPart.GetPartById(workbookPart.Workbook.Descendants <Sheet>().Where(e => e.Name.ToString().ToUpper().Equals(sheetName.ToUpper())).First().Id.Value) as WorksheetPart;
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
Row currentRow = sheetData.Elements <Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();
sheetData.RemoveChild(currentRow);
IEnumerable <Row> rows = worksheetPart.Worksheet.Descendants <Row>().Where(r => r.RowIndex.Value >= rowIndex + 1);
foreach (Row row in rows)
{
uint newIndex = row.RowIndex - 1;
string curRowIndex = row.RowIndex.ToString();
string newRowIndex = newIndex.ToString();
foreach (Cell cell in row.Elements <Cell>())
{
// Update the references for the rows cells.
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(curRowIndex, newRowIndex));
}
row.RowIndex = newIndex;
}
//worksheet.Save();
}
/// <summary>
/// Deletes the A work sheet.
/// </summary>
/// <param name="fileName">Name of the file.</param>
/// <param name="sheetToDelete">The sheet to delete.</param>
public void ClearWorkSheetData(string fileName, string sheetToClear)
{
string Sheetid = "";
//Open the workbook
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart wbPart = document.WorkbookPart;
// Get the pivot Table Parts
IEnumerable <PivotTableCacheDefinitionPart> pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts;
Dictionary <PivotTableCacheDefinitionPart, string> pvtTableCacheDefinationPart = new Dictionary <PivotTableCacheDefinitionPart, string>();
foreach (PivotTableCacheDefinitionPart Item in pvtTableCacheParts)
{
PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition;
//Check if this CacheSource is linked to SheetToDelete
var pvtCahce = pvtCacheDef.Descendants <CacheSource>().Where(s => s.WorksheetSource.Sheet == sheetToClear);
if (pvtCahce.Count() > 0)
{
pvtTableCacheDefinationPart.Add(Item, Item.ToString());
}
}
foreach (var Item in pvtTableCacheDefinationPart)
{
wbPart.DeletePart(Item.Key);
}
//Get the SheetToDelete from workbook.xml
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetToClear).FirstOrDefault();
if (theSheet == null)
{
// The specified sheet doesn't exist.
}
//Store the SheetID for the reference
Sheetid = theSheet.SheetId;
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
for (int childIndex = 1; childIndex < sheetData.ChildElements.Count; childIndex++)
{
sheetData.RemoveChild(sheetData.ChildElements[childIndex]);
}
IEnumerable <Row> rows = sheetData.Descendants <Row>();
List <Row> rowsList = rows.ToList();
//rowsList.RemoveRange(1, rowsList.Count - 1);
// Save the workbook.
wbPart.Workbook.Save();
}
}
/// <summary>
/// Builds the workbook.
/// </summary>
/// <param name="fileName">Name of the file.</param>
/// <param name="inputData">The input data.</param>
/// <param name="overwriteContents"></param>
/// <param name="clearHeader"></param>
public void SaveWorkbook(string fileName, DataSet inputData, bool overwriteContents, bool clearHeader)
{
SpreadsheetDocument spreadsheetDocument;
bool isNewFile = false;
try
{
if (!File.Exists(fileName))
{
spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook); //Create
isNewFile = true;
}
else
{
spreadsheetDocument = SpreadsheetDocument.Open(fileName, true); //Opening exisiting
isNewFile = false;
}
if (true)//using (spreadsheetDocument)
{
WorkbookPart workbookPart;
//Instantiates workbookpart
if (isNewFile)
{
workbookPart = spreadsheetDocument.AddWorkbookPart(); //If file is new file
}
else
{
workbookPart = spreadsheetDocument.WorkbookPart;
}
//Creates Workbook if workbook not existed(For New Excel File), Existing excel file this is not necessary
if (workbookPart.Workbook == null)
{
workbookPart.Workbook = new Workbook();
}
Sheets sheets;
//
if (isNewFile)
{
sheets = workbookPart.Workbook.AppendChild(new Sheets()); //Adding new sheets to the file, if new file
}
else
{
sheets = workbookPart.Workbook.Sheets; //Retrieving existing sheets from the file
}
FileVersion fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
// workbook.Append(fileVersion);
uint tableIndex = 0;
//converting the sheets collection to a list of <Sheet>
List <Sheet> sheetsList = workbookPart.Workbook.Descendants <Sheet>().ToList();
// If the InputDataSet having 1 or more tables, looping through and
// creates new sheet for each table and dumps the data to the sheet
// and saves the excel workbook.
foreach (DataTable inputDataTable in inputData.Tables)
{
bool hasSheetExists = false;
int sheetIndex = 0;
string relId = "";
Sheet sheet;
//Checking sheet exists in the excel file.
sheetIndex = sheetsList.FindIndex(c => c.Name == inputDataTable.TableName);
if (sheetIndex >= 0)
{
hasSheetExists = true;
}
WorksheetPart worksheetPart;
if (!hasSheetExists) //If a new sheet
{
worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
relId = workbookPart.GetIdOfPart(worksheetPart);
sheet = new Sheet {
Name = inputDataTable.TableName, SheetId = tableIndex + 1, Id = relId
};
}
else // if sheet already exists
{
sheet = sheetsList[sheetIndex];
List <WorksheetPart> partList = workbookPart.WorksheetParts.ToList();
// Take the existing the sheet reference from the workbook.
WorksheetPart worksheetPart1 = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
worksheetPart = worksheetPart1;
//Retriving RelationID form the workbookPart
relId = workbookPart.GetIdOfPart(worksheetPart);
// partList.FindAll( c=> c.)
// worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
}
SheetData sheetData = new SheetData();
Worksheet workSheet = worksheetPart.Worksheet;
int lastRowIndex = 0;
if (workSheet != null)
{
//Retrieving existing sheet data from the worksheet
sheetData = workSheet.GetFirstChild <SheetData>();
if (overwriteContents) //Clearing the contents of the Sheet, except the header
{
int endIndex = 1;
//if value true clear the existing header
if (clearHeader)
{
endIndex = 0;
}
//Deleting content row by row, starting from the bottom.
for (int childIndex = sheetData.ChildElements.Count - 1; childIndex >= endIndex; childIndex--)
{
sheetData.RemoveChild(sheetData.ChildElements[childIndex]);
}
}
//Getting all existing record rows.
IEnumerable <Row> rows = sheetData.Descendants <Row>();
//Considering the last row index as total row count. append the records to the last index onwards.
lastRowIndex = rows.Count();
}
else //Creating new worksheet
{
workSheet = new Worksheet(sheetData);
worksheetPart.Worksheet = workSheet;
}
//If Data Table is not empty
if (inputDataTable != null && inputDataTable.Rows.Count > 0)
{
//If Not Sheet already exists(Based on Table Name) -- creating column headers for the excel sheet
if (!hasSheetExists || lastRowIndex < 1)
{
//Creating columns..(INDX = 1 , Header)
Row headerRow = CreateContentHeader(1, inputDataTable.Columns);
sheetData.Append(headerRow);
lastRowIndex = 1;
}
//Last Row index
lastRowIndex++;
//Worksheet Data Row Number.. (INDX = 2 onwards data)
uint currDataRowIndex = (uint)lastRowIndex; //From this index on data will get appended.
//Creating Row Data
for (int iterRowIndex = 0; iterRowIndex < inputDataTable.Rows.Count; iterRowIndex++)
{
//Retrieving current DataRow from DataTable
DataRow currentInputRow = inputDataTable.Rows[iterRowIndex];
//Creating insertble row for the openxml.
Row contentRow = CreateContentRow(currDataRowIndex, currentInputRow,
inputDataTable.Columns);
currDataRowIndex++;
//Appending to sheet data
sheetData.AppendChild(contentRow);
}
}
//new Worksheet(sheetData);
//Saving worksheet contents
worksheetPart.Worksheet.Save();
//If sheet new, then appending to sheets collection
if (!hasSheetExists)
{
sheets.AppendChild(sheet);
}
tableIndex++;
}
//Saving the complete workbook to disk
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
}
catch (Exception e)
{
// spreadsheetDocument.Close();
throw;
}
}