DocumentFormat.OpenXml.dll version 250 (copy into script folder)
WindowsBase.dll version 403 (copy into script folder)
Code: Select all
myCode =
(
using System;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
class myClass
{
public void myMethod()
{
string myPath = Directory.GetCurrentDirectory() + @"\myExcelSample.xlsx";
using (SpreadsheetDocument d = SpreadsheetDocument.Create(myPath, SpreadsheetDocumentType.Workbook))
{
WorkbookPart myWBPart = d.AddWorkbookPart();
myWBPart.Workbook = new Workbook();
Sheets mySheets = myWBPart.Workbook.AppendChild(new Sheets());
WorksheetPart myWSPart = null;
SharedStringTablePart mySSTBLPart = myWBPart.AddNewPart<SharedStringTablePart>();
string mySheetName = "";
string myContents = "";
for (int mySheetCount = 1; mySheetCount < 11; mySheetCount++)
{
mySheetName = "mySheet" + mySheetCount.ToString();
myWSPart = wsADD(d.WorkbookPart, mySheetName);
for (uint i = 1; i <= 10; i++)
{
myContents = DateTime.Now.ToShortTimeString() + " " + Regex.Replace(DateTime.Now.Ticks.ToString(), ".*(....)", "$1") + " Ticks";
textInject(d, myWSPart, convertColumnName((int)i) + i, myContents);
}
}
}
}
public static void textInject(SpreadsheetDocument d, WorksheetPart ws파트, string 셀주소, string 문자열)
{
string 열이름 = Regex.Replace(셀주소, @"([a-zA-Z]+)(\d+)", "$1");
uint 행색인;
UInt32.TryParse(Regex.Replace(셀주소, @"([a-zA-Z]+)(\d+)", "$2"), out 행색인);
SharedStringTablePart ssTBL파트;
if (d.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
ssTBL파트 = d.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
else
ssTBL파트 = d.WorkbookPart.AddNewPart<SharedStringTablePart>();
int ss색인 = ss아이템주입(문자열, ssTBL파트);
Cell c = 워크시트에셀추가(열이름, 행색인, ws파트);
c.CellValue = new CellValue(ss색인.ToString());
c.DataType = new EnumValue<CellValues>(CellValues.SharedString);
ws파트.Worksheet.Save();
}
private static int ss아이템주입(string 텍스트, SharedStringTablePart ssTBL)
{
if (ssTBL.SharedStringTable == null)
ssTBL.SharedStringTable = new SharedStringTable();
int 색인 = 0;
foreach (SharedStringItem 아이템 in ssTBL.SharedStringTable.Elements<SharedStringItem>())
{
if (아이템.InnerText == 텍스트)
return 색인;
색인++;
}
ssTBL.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(텍스트)));
ssTBL.SharedStringTable.Save();
return 색인;
}
private static WorksheetPart wsADD(WorkbookPart wb파트, string 시트이름)
{
WorksheetPart ws파트 = wb파트.AddNewPart<WorksheetPart>();
ws파트.Worksheet = new Worksheet(new SheetData());
Sheets 시트즈 = wb파트.Workbook.GetFirstChild<Sheets>();
string rID = wb파트.GetIdOfPart(ws파트);
uint sID = 1;
if (시트즈.Elements<Sheet>().Count() > 0)
sID = 시트즈.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
Sheet 시트 = new Sheet()
{
Id = rID,
SheetId = sID,
Name = 시트이름
};
시트즈.Append(시트);
wb파트.Workbook.Save();
return ws파트;
}
private static Cell 워크시트에셀추가(string 열이름, uint 행색인, WorksheetPart ws파트)
{
Worksheet ws = ws파트.Worksheet;
SheetData 시트데이터 = ws.GetFirstChild<SheetData>();
string 셀참조 = 열이름 + 행색인;
Row 행수;
if (시트데이터.Elements<Row>().Where(r => r.RowIndex == 행색인).Count() != 0)
행수 = 시트데이터.Elements<Row>().Where(r => r.RowIndex == 행색인).First();
else
{
행수 = new Row() { RowIndex = 행색인 };
시트데이터.Append(행수);
}
if (행수.Elements<Cell>().Where(c => c.CellReference.Value == 열이름 + 행색인).Count() > 0)
return 행수.Elements<Cell>().Where(c => c.CellReference.Value == 셀참조).First();
else
{
Cell 기준셀 = null;
foreach (Cell 셀 in 행수.Elements<Cell>())
if (string.Compare(셀.CellReference.Value, 셀참조, true) > 0)
{
기준셀 = 셀;
break;
}
Cell 새론셀 = new Cell() { CellReference = 셀참조 };
행수.InsertBefore(새론셀, 기준셀);
ws.Save();
return 새론셀;
}
}
private static string convertColumnName(int 칼럼색인)
{
if(칼럼색인 < 1)
return String.Empty;
return convertColumnName((칼럼색인 - 1) / 26) + (char)('A' + (칼럼색인 - 1) `% 26);
}
}
)
myReference =
( Join|
System.dll
System.IO.dll
System.Core.dll
DocumentFormat.OpenXml.dll
WindowsBase.dll
)
CLR_CreateObject(CLR_CompileC#(myCode, myReference), "myClass").myMethod()