/// <summary>
/// Removes the sheet from the document
/// using the given sheet unique name
/// </summary>
/// <param name="document">SpreadSheetDocument reference</param>
/// <param name="name">Sheet name</param>
public static void RemoveSheet(this SpreadsheetDocument document, string name)
{
//Check whether the document or the sheet name is a non-valid to work with object value
if (document.IsNotValid() || name.IsNotValid())
{
return;
}
//Search for sheet collection reference
Sheets sheets = document
.WorkbookPart
.Workbook
.GetFirstChild <Sheets>();
//If there is not any existing sheet with the given name, there is nothing to remove.
if (!sheets.Descendants <Sheet>().Any(s => s.Name.Equals(name)))
{
return;
}
Sheet sheet = sheets.Descendants <Sheet>()
.First(s => s.Name.Equals(name));
int index = sheets
.ToList()
.IndexOf(sheet);
sheet.RemoveAllChildren();
sheet.Remove();
}
public IEnumerable <T> GetRows <T>(string sheetName) where T : IRecordable, new()
{
Sheet sheet = sheets.Descendants <Sheet>().FirstOrDefault(sheet => sheet.Name == sheetName);
if (sheet != null)
{
return(GetRows <T>(sheet, null));
}
else
{
return(Enumerable.Empty <T>());
}
}
/// <summary>
/// Adds a new Sheet to the document
/// </summary>
/// <param name="document">SpreadSheetDocument reference</param>
/// <param name="name">Sheet name</param>
public static void AddSheet(this SpreadsheetDocument document, string name)
{
//Check whether the document or the sheet name is a non-valid to work with object value
if (document.IsNotValid() || name.IsNotValid())
{
return;
}
//Search for sheet collection reference
Sheets sheets = document
.WorkbookPart
.Workbook
.GetFirstChild <Sheets>();
//If there already exists a sheet by the given name, there is no need to create a new one.
if (sheets.Descendants <Sheet>().Any(s => s.Name.Equals(name)))
{
return;
}
//Get the last sheet pushed into the sheets collection
Sheet last = sheets
.Descendants <Sheet>()
.OrderByDescending(s => s.SheetId)
.FirstOrDefault();
//Set current workspace reference
WorksheetPart workSpace = document.WorkbookPart.AddNewPart <WorksheetPart>();
//Add new worksheet with data
workSpace.Worksheet = new Worksheet(new SheetData());
//Stablish index value
uint index = last == null ? 1 : last.SheetId.Value;
index = last == null ? 1 : index + 1;
//Create the new sheet
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(workSpace),
SheetId = index,
Name = name
};
//Add new sheet to the collection
sheets.Append(sheet);
}
public void ChangeSheetName(int idx, string name)
{
Sheets sheets = this.document.WorkbookPart.Workbook.GetFirstChild <Sheets>();
Sheet sheet = sheets.Descendants <Sheet>().Where(x => x.SheetId == (uint)idx).FirstOrDefault();
if (sheet != null)
{
sheet.Name = name;
}
}
private Worksheet GetWorksheet(string sheetName, Sheets sheets, WorkbookPart workbookPart)
{
Sheet sheet = sheets.Descendants <Sheet>().FirstOrDefault(sheet => sheet.Name == sheetName);
if (sheet == null)
{
return(null);
}
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
return(worksheetPart.Worksheet);
}
/// <summary>
/// Gets the next sheet identifier.
/// </summary>
/// <param name="sheets">The sheets.</param>
/// <returns></returns>
public static UInt32Value GetNextSheetId(this Sheets sheets)
{
uint next = (uint)sheets.ChildElements.Count + 1;
bool keepTrying = true;
while (keepTrying)
{
bool match = (from s in sheets.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>()
where s.SheetId.HasValue &&
s.SheetId.Value.CompareTo(next) == 0
select s).Any();
if (!match)
{
keepTrying = false;
break;
}
next++;
}
return(next);
}
public void LoadXlsx(string path)
{
// Буфер - чтобы не держать файл открытым.
MemoryStream memoryStream = new MemoryStream();
using (Stream stream = File.Open(path, FileMode.Open, FileAccess.Read))
{
stream.CopyTo(memoryStream);
}
using (SpreadsheetDocument package = SpreadsheetDocument.Open(memoryStream, false))
{
WorkbookPart workbookPart = package.WorkbookPart;
SharedStringTablePart sharedStringTablePart = workbookPart.SharedStringTablePart;
this.sharedStringTable = sharedStringTablePart.SharedStringTable;
WorkbookStylesPart workbookStylesPart = workbookPart.WorkbookStylesPart;
this.stylesheet = workbookStylesPart.Stylesheet;
Workbook workbook = workbookPart.Workbook;
Sheets sheets = workbook.Sheets;
foreach (Sheet sheet in sheets.Descendants <Sheet>())
{
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
Worksheet worksheet = worksheetPart.Worksheet;
// размеры
XDimention xd = new XDimention(worksheet.SheetDimension.Reference);
// добавляем новый лист в представление Net
XWorksheet xWorksheet = new XWorksheet(xd);
XWorksheets.Add(xWorksheet);
xWorksheet.Name = sheet.Name;
// разбор свойств листа
parseSheetProperties(worksheet, xWorksheet);
// разбор данных
parseSheetData(worksheet, xWorksheet);
// разбор объединений ячеек
parseMegreCells(worksheet, xWorksheet);
}
}
memoryStream.Dispose();
}
//見出しを書き込む
public override void WriteHeader(List <DataItems> Header)
{
if (Items != null)
{
return;
}
SpreadsheetDocument Doc = null;
if (File.Exists(FileName))
{
try
{
Doc = SpreadsheetDocument.Open(FileName, true);
}
catch (Exception)
{
throw new Exception("データファイル「" + FileName + "」を開けませんでした");
}
}
else
{
Doc = SpreadsheetDocument.Create(FileName, SpreadsheetDocumentType.Workbook);
}
try
{
WorkbookPart BPrt = Doc.WorkbookPart;
Sheet MeiboSheet = null;
if (BPrt == null)
{
BPrt = Doc.AddWorkbookPart();
BPrt.Workbook = new Workbook();
}
else
{
//シート「名簿作成システム」を探す
MeiboSheet = BPrt.Workbook.Descendants <Sheet>().Where(s => s.Name == "名簿作成システム").FirstOrDefault();
}
WorksheetPart SPrt;
var SData = new SheetData();
if (MeiboSheet != null)
{
throw new Exception("すでに本プログラム用のシートがあります");
}
SPrt = BPrt.AddNewPart <WorksheetPart>(); //新規作成
SPrt.Worksheet = new Worksheet(SData);
Sheets sheets = BPrt.Workbook.Descendants <Sheets>().FirstOrDefault();
bool FirstSheetFlg = false;
if (sheets == null)
{//新規でシート群(Sheets)作成
sheets = BPrt.Workbook.
AppendChild <Sheets>(new Sheets());
FirstSheetFlg = true;
}
MeiboSheet = new Sheet()
{
Id = Doc.WorkbookPart.GetIdOfPart(SPrt),
SheetId = FirstSheetFlg ? 1 : sheets.Descendants <Sheet>().Last <Sheet>().SheetId + 1,
Name = "名簿作成システム"
};
sheets.Append(MeiboSheet);
var FirstRow = new Row();
for (int i = 0; i < Header.Count; i++) //項目を追加
{
FirstRow.Append(new Cell()
{
DataType = CellValues.String,
CellReference = (char)((int)'A' + i) + "1",
CellValue = new CellValue(EnumToLabel[(int)Header[i]])
});
}
SData.Append(FirstRow);
}
finally
{
if (Doc != null)
{
Doc.Close();
}
}
Items = Header;
}
