Here's the content of the class I use to write an excel file based on content from a SQlite DB we use to store electrical schematik related information. Then I read the data from an IO list file made the the electrical department. I compare with balloons in the DrawingSheet in order to import related data into a custom table in the same Sheet.
using Schematik;
using Inventor;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Linq;
using System.Linq;
using System.Windows.Forms;
using GenikAddin.DataControl;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.Globalization;
namespace GenikAddin
{
public static class ExcelExtension
{
public static string OpenExcelFile(string documentPath)
{
string basePath = "";
basePath = SchematikMethod.GetProjectESFolder(SchematikMethod.GetProjectFolder(documentPath));
string filePath = "";
using (OpenFileDialog openFileDialog = new OpenFileDialog())
{
openFileDialog.Filter = "excel files (*.xlsx)|*.xlsx;*.xls|All files (*.*)|*.*";
openFileDialog.FilterIndex = 1;
openFileDialog.InitialDirectory = basePath;
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog(new Form() { TopMost = true }) == DialogResult.OK)
{
filePath = openFileDialog.FileName;
}
}
return filePath;
}
public static string SaveExcelFile(string documentPath)
{
//document path is used as a base for default folder open option
string basePath = "";
string projectPath = SchematikMethod.GetProjectFolder(documentPath);
basePath = SchematikMethod.GetProjectESFolder(projectPath);
//if basePath was returned equal to projectPath, do not search for Exportation.
if (!(projectPath.Equals(basePath, StringComparison.CurrentCultureIgnoreCase)))
{
//add Exportation folder to path
basePath = System.IO.Path.Combine(basePath, "Exportation");
if (!Directory.Exists(basePath))
{
//if folder does not exists, add it
Directory.CreateDirectory(basePath);
//Also add a _OLD subfolder
Directory.CreateDirectory(System.IO.Path.Combine(basePath, "_OLD"));
}
}
string filePath = "";
using (SaveFileDialog saveFileDialog = new SaveFileDialog())
{
saveFileDialog.Filter = "excel files (*.xlsx)|*.xlsx;*.xls|All files (*.*)|*.*";
saveFileDialog.FilterIndex = 1;
saveFileDialog.InitialDirectory = basePath;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.FileName = SchematikMethod.GetProjectNumber(documentPath) + "_EXPORT LISTE ES_" + DateTime.Now.ToString("yyyy-MM-dd hh'h'mm");
if (saveFileDialog.ShowDialog(new Form() { TopMost = true }) == DialogResult.OK)
{
filePath = saveFileDialog.FileName;
}
}
return filePath;
}
public static void GenerateSchematikExcelFile2(List<ESComponent> components, string excelFilePath)
{
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(excelFilePath, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
// Append a new worksheet and associate it with the workbook.
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Schematik"
};
sheets.Append(sheet);
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Any())
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
//Start inserting values to spreadsheet
DocumentFormat.OpenXml.Spreadsheet.Row row;
uint rowIndex = 1;
//Generating headers
row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex };
sheetData.Append(row);
AddCellToSchematik("TagMec", "B", 1, row, shareStringPart);
AddCellToSchematik("Module", "C", 1, row, shareStringPart);
AddCellToSchematik("Section", "D", 1, row, shareStringPart);
AddCellToSchematik("Marque", "E", 1, row, shareStringPart);
AddCellToSchematik("Modèle", "F", 1, row, shareStringPart);
AddCellToSchematik("Description", "G", 1, row, shareStringPart);
AddCellToSchematik("Caractéristique du câble", "L", 1, row, shareStringPart);
AddCellToSchematik("Orientation du câble", "M", 1, row, shareStringPart);
AddCellToSchematik("Caractéristique du connecteur", "N", 1, row, shareStringPart);
//AddCellToSchematik("Position", "V", 1, row, shareStringPart);
AddCellToSchematik("Type D'I/O", "X", 1, row, shareStringPart);
AddCellToSchematik("Status (0 = inchangé, 1 = ajouté, 2 = modifié, 3 = Retiré)", "Z", 1, row, shareStringPart);
foreach (ESComponent component in components)
{
//Reorder list based on position value
List<InOut> inOuts = new List<InOut>(component.InOuts.OrderBy(x => x.Position));
foreach (InOut inOut in inOuts)
{
if (inOut.IOType != "AUCUN" && !string.IsNullOrWhiteSpace(inOut.IOType))
{
rowIndex++;
row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex };
sheetData.Append(row);
AddCellToSchematik(component.TagMec + "-" + component.Id, "B", rowIndex, row, shareStringPart);
AddCellToSchematik(component.Module, "C", rowIndex, row, shareStringPart);
AddCellToSchematik(component.Location, "D", rowIndex, row, shareStringPart);
AddCellToSchematik(component.Mfr, "E", rowIndex, row, shareStringPart);
AddCellToSchematik(component.PartNumber, "F", rowIndex, row, shareStringPart);
AddCellToSchematik(inOut.Description, "G", rowIndex, row, shareStringPart);
AddCellToSchematik(component.CableInfo, "L", rowIndex, row, shareStringPart);
AddCellToSchematik(component.Orientation ? "90" : "0", "M", rowIndex, row, shareStringPart);
AddCellToSchematik(component.ConnectorInfo, "N", rowIndex, row, shareStringPart);
//AddCellToSchematik(inOut.Position.ToString(), "V", rowIndex, row, shareStringPart);
AddCellToSchematik(inOut.IOType, "X", rowIndex, row, shareStringPart); //Change to Y when IO list change
AddCellToSchematik(component.Status.ToString(), "Z", rowIndex, row, shareStringPart);
}
}
// reset component status after export
if (component.Status != 3)
{
component.Status = 0;
}
}
workbookpart.Workbook.Save();
}
}
private static void AddCellToSchematik(string text, string column, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.Row row, SharedStringTablePart shareStringPart)
{
// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(text, shareStringPart);
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = column + rowIndex };
row.AppendChild(newCell);
newCell.CellValue = new CellValue(index.ToString());
newCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
// 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;
}
public static void GenerateSchematikExcelFile(List<ESComponent> components, string excelFilePath = null)
{
try
{
if (components == null || components.Count == 0)
throw new Exception("GenerateExcelFile: Null or empty input table!\n");
// load excel, and create a new workbook
var excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;
workSheet.Name = "Schematik";
//File Headers
workSheet.Cells[1, 1] = "TagMec";
workSheet.Cells[1, 2] = "Module";
workSheet.Cells[1, 3] = "Localisation";
workSheet.Cells[1, 4] = "Mfr";
workSheet.Cells[1, 5] = "PartNo";
workSheet.Cells[1, 6] = "Description";
workSheet.Cells[1, 7] = "InfoCable";
workSheet.Cells[1, 8] = "InfoConnecteur";
workSheet.Cells[1, 9] = "IOType";
workSheet.Cells[1, 10] = "Position";
// rows
int pos = 1;
foreach (ESComponent component in components)
{
//Reorder list based on position value
List<InOut> inOuts = new List<InOut>(component.InOuts.OrderBy(x => x.Position));
foreach (InOut inOut in inOuts)
{
if(inOut.IOType != "AUCUN" && !string.IsNullOrWhiteSpace(inOut.IOType))
{
pos++;
workSheet.Cells[pos, 1] = component.TagMec + "-" + component.Id;
workSheet.Cells[pos, 2] = component.Module;
workSheet.Cells[pos, 3] = component.Location;
workSheet.Cells[pos, 4] = component.Mfr;
workSheet.Cells[pos, 5] = component.PartNumber;
workSheet.Cells[pos, 6] = inOut.Description;
workSheet.Cells[pos, 7] = component.CableInfo;
workSheet.Cells[pos, 8] = component.ConnectorInfo;
workSheet.Cells[pos, 9] = inOut.IOType;
workSheet.Cells[pos, 10] = inOut.Position;
}
}
}
// check file path
if (!string.IsNullOrEmpty(excelFilePath))
{
try
{
workSheet.SaveAs(excelFilePath);
excelApp.Quit();
MessageBox.Show(new Form() { TopMost = true }, "Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("GenerateExcelFile: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else
{ // no file path is given
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("GenerateExcelFile: \n" + ex.Message);
}
}
public static void ReadIOFromExcel(string excelFilePath, Inventor.Sheet sheet)
{
const string TagMec = "B";
const string Description1 = "G";
const string Description2 = "H";
const string Description3 = "I";
const string Description4 = "J";
const string TagEle = "R"; //Change to S when IO list change
//fetch balloon values from sheet
List<string> tagMecList = new List<string>();
foreach(Balloon balloon in sheet.Balloons)
{
tagMecList.Add(balloon.BalloonValueSets[1].OverrideValue);
}
if (tagMecList.Count == 0)
{
throw new Exception("La feuille active ne contient pas de ballons !");
}
//make sure values are unique
tagMecList = tagMecList.Distinct().ToList();
//look for table in drawing
CustomTables tables = sheet.CustomTables;
Inventor.Point2d tablePos = null;
if (sheet.Border is Inventor.Border border)
{
tablePos = border.RangeBox.MaxPoint;
}
else
{
tablePos = AddInServer.InvApp.TransientGeometry.CreatePoint2d(sheet.Width, sheet.Height);
}
//Delete table if already exists
foreach(Inventor.CustomTable table in tables)
{
if (table.Title.Equals("Schematik", StringComparison.CurrentCultureIgnoreCase))
{
table.Delete();
}
}
string[] columnTitles = { "ID", "TagMec","TagEle", "Description" };
CustomTable newTable = sheet.CustomTables.Add("Schematik", AddInServer.InvApp.TransientGeometry.CreatePoint2d(0, 0), 4, 1, columnTitles);
List<SchematikTableRow> schematikTableRows = new List<SchematikTableRow>();
SchematikTableRow schematikTableRow = null;
//open file in readonly fashion
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(excelFilePath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
//Get worksheet id named "Table IO"
var sheetId = workbookPart.Workbook.Descendants<DocumentFormat.OpenXml.Spreadsheet.Sheet>().FirstOrDefault(x => x.Name.Equals("Table IO"))?.Id;
WorksheetPart worksheetPart = sheetId != null ? (WorksheetPart)workbookPart.GetPartById(sheetId) : workbookPart.WorksheetParts.First();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); //generate the reader
string text;
string value = null;
int actualRow = 1;
while (reader.Read())
{
if (reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Cell))
{
if ((DocumentFormat.OpenXml.Spreadsheet.Cell)reader.LoadCurrentElement() is DocumentFormat.OpenXml.Spreadsheet.Cell cell && cell.DataType != null)
{
if (!string.IsNullOrWhiteSpace(cell.InnerText))
{
string column = "";
int row = 0;
//use cellReference to know column and row position
if (cell.CellReference != null && cell.CellReference.HasValue)
{
column = string.Concat(cell.CellReference.InnerText.Where(x => !char.IsDigit(x)).Select(x => x));
//parsing is guaranteed since only digits are fetched from the string
row = int.Parse(string.Concat(cell.CellReference.InnerText.Where(x => char.IsDigit(x)).Select(x => x)));
}
//IO list template values starts at 4
if(row > 3 && (column == TagMec || column == Description1 || column == Description2 || column == Description3 || column == Description4 || column == TagEle))
{
//Create New Inventor TalbeRow only when pointer change to a new row
if (row > actualRow + 2) //offset values
{
schematikTableRow = new SchematikTableRow
{
Id = actualRow + 3
};
schematikTableRows.Add(schematikTableRow);
actualRow++;
}
value = cell.InnerText;
switch (cell.DataType.Value)
{
//fetch actual cellValue if it comes from the shared string table
case CellValues.SharedString:
var stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTable != null)
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
switch (column)
{
case TagMec:
schematikTableRow.TagMec = value;
break;
case Description1:
schematikTableRow.Description1 = value;
break;
case Description2:
schematikTableRow.Description2 = value;
break;
case Description3:
schematikTableRow.Description3 = value;
break;
case Description4:
schematikTableRow.Description4 = value;
break;
case TagEle:
schematikTableRow.TagEle = value;
break;
}
}
}
}
text = value;
}
}
reader.Dispose();
}
Inventor.Row tableRow = null;
//generate table
int descColWidth = 1;
int idColWidth = 1;
int tagMecColWidth = 1;
int tagEleColWidth = 1;
//Filter list to contain only one occurrence of any TagELE in case of valve manifold which can contain 32 lines with only one TagELE
schematikTableRows = new List<SchematikTableRow>(schematikTableRows.GroupBy(x => x.TagEle).Select(x => x.FirstOrDefault()).ToList().OrderBy(x => x.Id));
foreach (SchematikTableRow row in schematikTableRows)
{
if (tagMecList.Where(x => x.Equals(row.TagMec, StringComparison.CurrentCultureIgnoreCase)).Any())
{
string desc = string.Join(" ", new string[] { row.Description1, row.Description2, row.Description3, row.Description4 });
tableRow = newTable.Rows.Add();
tableRow[1].Value = row.Id.ToString();
tableRow[2].Value = row.TagMec;
tableRow[3].Value = row.TagEle;
tableRow[4].Value = desc;
//set columns width values
idColWidth = idColWidth < row.Id.ToString().Length ? row.Id.ToString().Length : idColWidth;
tagMecColWidth = tagMecColWidth < row.TagMec.Length ? row.TagMec.Length : tagMecColWidth;
tagEleColWidth = tagEleColWidth < row.TagEle.Length ? row.TagEle.Length : tagMecColWidth;
descColWidth = descColWidth < desc.Length ? desc.Length : descColWidth;
}
}
//Adjust columns width
newTable.Columns[1].Width = idColWidth * 0.3;
newTable.Columns[2].Width = tagMecColWidth * 0.3;
newTable.Columns[3].Width = tagEleColWidth * 0.3;
newTable.Columns[4].Width = descColWidth * 0.25;
//Delete first row if empty
if (string.IsNullOrWhiteSpace(newTable.Rows[1][1].Value))
{
newTable.Rows[1].Delete();
}
tablePos.X = tablePos.X - newTable.RangeBox.MaxPoint.X - 0.3175;
tablePos.Y = tablePos.Y - newTable.RangeBox.MaxPoint.Y - 0.3175;
newTable.Position = tablePos;
}
private class SchematikTableRow
{
public int Id { get; set;}
public string TagMec { get; set; }
public string TagEle { get; set; }
public string Description1 { get; set; }
public string Description2 { get; set; }
public string Description3 { get; set; }
public string Description4 { get; set; }
}
}
}
