AHK/CLR/OpenXml - Office documents w/o MS Excel

[Klarion]

.NET Framework version 452
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()

[Klarion]

How to read MS Excel file, simply (!)
https://www.autohotkey.com/boards/viewtopic.php?f=6&t=29408

[Klarion]

Actually, OOXML Excel is Crazy
You have to handle millions of secret lines of codes, Pity.

I'd rather use EPPlus

See following codes
It is just 6 lines

Code: Select all

myFile := "myTesting.xlsx"				
myPackage := CLR_CreateObject(CLR_LoadLibrary("EPPlus.dll"), "OfficeOpenXml.ExcelPackage")    
myWorkSheet := myPackage.Workbook.Worksheets.Add("mySheet1")
myRow := 2, myColumn := 2
myWorkSheet.SetValue(myRow, myColumn, "Hello Excel  !!")  
myPackage.SaveAs(CLR_CreateObject(CLR_LoadLibrary("mscorlib"), "System.IO.FileInfo", myFile))

Thanks burque505

[burque505]

You bet, Klarion. I hope more people will start experimenting with DotNet DLLs and AHK. There are so many out there to leverage.

[ovidiugabriel]

I would like to use this in my project.

The trouble with this is that I want to use AHK/CLR/OpenXml without writing C# glue code for this.
Sometimes the OpenXml library requires invoking static methods like

Code: Select all

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);

which, from what I see, cannot be done with CLR.ahk solely.

Any ideas on how to get to that?
beside Dynamically Binding to Static (Class-Scoped) Members (requires C# wrapper class) - and, of course, without writing glue code in C#