private Cell CreateTextCell(int columnIndex, int rowIndex, object cellValue, RunProperties runProperties = null)
{
Cell cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = GetColumnName(columnIndex) + rowIndex,
};
Text text = new Text
{
Text = cellValue.ToString()
};
RunSpreadsheet run = new RunSpreadsheet();
run.Append(text);
if (runProperties != null)
{
run.RunProperties = runProperties;
}
InlineString inlineString = new InlineString();
inlineString.Append(run);
cell.AppendChild(inlineString);
return(cell);
}
/// <summary>
/// Crea una celda en una posicion especificada con un valor definido
/// Se define si el campo va en negrita (bold)
/// Opcionalmente se puede establecer el tamaño de la fuente
/// </summary>
/// <param name="posicion"></param>
/// <param name="valor"></param>
/// <param name="bold"></param>
/// <param name="fontSize"></param>
/// <returns></returns>
private static Cell crearCelda(string posicion, string valor, bool bold, int fontSize = 12)
{
Cell celda = new Cell()
{
DataType = CellValues.InlineString,
CellReference = posicion
};
RunProperties runProperties = new RunProperties();
if (bold)
{
runProperties.Append(new Bold());
}
runProperties.Append(new FontSize()
{
Val = fontSize
});
Run run = new Run();
run.Append(new Text(valor));
run.RunProperties = runProperties;
InlineString inlineString = new InlineString();
inlineString.Append(run);
celda.Append(inlineString);
return(celda);
}
/// <summary>
/// Creates text cell
/// </summary>
/// <param name="header">the header</param>
/// <param name="index">the index</param>
/// <param name="text">the text content</param>
/// <returns>The cell</returns>
internal static Cell CreateTextCell(string header, uint index, string text)
{
Cell cell = new Cell {
DataType = CellValues.InlineString, CellReference = header + index
};
InlineString istring = new InlineString();
istring.Append(new Text {
Text = text
});
cell.StyleIndex = 8;
cell.Append(istring);
return(cell);
}
/// <summary>
/// Creates aligned text cell
/// </summary>
/// <param name="text">the text content</param>
/// <returns>The cell</returns>
internal static Cell CreateAlignedTextCell(string text)
{
Cell cell = new Cell {
DataType = CellValues.InlineString
};
InlineString istring = new InlineString();
istring.Append(new Text {
Text = text
});
cell.StyleIndex = 13;
cell.Append(istring);
return(cell);
}
// Create a text cell
private Cell CreateTextCell(string header, UInt32 index, string text)
{
var cell = new Cell();
cell.CellReference = header + index;
cell.DataType = CellValues.InlineString;
var istring = new InlineString();
var cellText = new Text {
Text = text
};
istring.Append(cellText);
cell.Append(istring);
return(cell);
}
public static void InsertValuesInWorksheet(WorksheetPart worksheetPart, uint rowIdx, List<string> values)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild<SheetData>();
Row row = new Row();
values.ForEach(v =>
{
Cell cell = new Cell() { DataType = CellValues.InlineString };
InlineString inlineString = new InlineString();
inlineString.Append(new Text() { Text = v });
cell.Append(inlineString);
row.Append(cell);
});
sheetData.Append(row);
}
/// <summary>
/// Creates the text cell.
/// </summary>
/// <param name="header">The header.</param>
/// <param name="index">The rowDataIndex.</param>
/// <param name="text">The text.</param>
/// <returns></returns>
private Cell CreateTextCell(string header, UInt32 index, object text)
{
Cell c = new Cell {
DataType = CellValues.InlineString, CellReference = header + index
};
InlineString istring = new InlineString();
Text t = new Text {
Text = Convert.ToString(text)
};
istring.Append(t);
c.Append(istring);
return(c);
}
private static void UpdateText(this Cell cell, string cellValue)
{
int resInt;
double resDouble;
DateTime resDate;
try
{
if (int.TryParse(cellValue, out resInt))
{
cell.CellValue = new CellValue(resInt.ToString());
cell.DataType = CellValues.String;
}
else if (double.TryParse(cellValue, out resDouble))
{
cell.CellValue = new CellValue(resDouble.ToString());
cell.DataType = CellValues.String;
}
else if (DateTime.TryParse(cellValue, out resDate))
{
cell.CellValue = new CellValue(resDate.ToString("dd/MM/yyyy"));
cell.DataType = CellValues.String;
}
else
{
string text = cellValue == null ? "0" : cellValue.ToString();
cell.CellValue = new CellValue(text);
InlineString inlineString = new InlineString();
Text txt = new Text();
txt.Text = text;
inlineString.Append(txt);
cell.InlineString = inlineString;
cell.DataType = CellValues.InlineString;
}
}
catch (Exception ex)
{
string excep = ex.Message;
}
}
// Generates content of worksheetPart1.
private static void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
{
var worksheet1 = new Worksheet();
var sheetData1 = new SheetData();
var row1 = new Row();
var cell1 = new Cell {
CellReference = "A1", DataType = CellValues.InlineString
};
var inlineString1 = new InlineString();
var text1 = new Text {
Text = "hello"
};
inlineString1.Append(text1);
cell1.Append(inlineString1);
row1.Append(cell1);
sheetData1.Append(row1);
worksheet1.Append(sheetData1);
worksheetPart1.Worksheet = worksheet1;
}
/// <summary>
/// Creates footer row
/// </summary>
/// <param name="index">The index</param>
/// <param name="dr">The data row</param>
/// <returns>The row</returns>
internal static Row CreateFooterRow(uint index, DataRow dr)
{
Row row = new Row {
RowIndex = index
};
foreach (object itm in dr.ItemArray)
{
Cell cell;
if (itm.GetType() == Type.GetType("System.Int32"))
{
cell = CreateNumberCell(Convert.ToInt32(itm));
}
else if (itm.GetType() == Type.GetType("System.Decimal"))
{
cell = CreateDecimalCell(Convert.ToDecimal(itm));
}
else
{
cell = new Cell {
DataType = CellValues.InlineString
};
InlineString istring = new InlineString();
Text t = new Text {
Text = itm.ToString()
};
istring.Append(t);
cell.StyleIndex = 12;
cell.Append(istring);
}
row.Append(cell);
}
return(row);
}
public static void InsertValuesInWorksheet(WorksheetPart worksheetPart, uint rowIdx, List <string> values)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild <SheetData>();
Row row = new Row();
values.ForEach(v =>
{
Cell cell = new Cell()
{
DataType = CellValues.InlineString
};
InlineString inlineString = new InlineString();
inlineString.Append(new Text()
{
Text = v
});
cell.Append(inlineString);
row.Append(cell);
});
sheetData.Append(row);
}
protected InlineString FormatMessage(string value, string cellReference)
{
var returnObject = new InlineString();
var anchors = Anchor.Find(value);
if (!anchors.Any())
{
returnObject.Append(new Run(new Text(value.TrimEnd(Environment.NewLine.ToCharArray()))
{
Space = SpaceProcessingModeValues.Preserve
}));
}
else
{
// Use the first instance because only one hyperlink per cell is allowed
var anchor = anchors[0];
var hyperLinkId = string.Concat(cellReference, "HyperLink");
WorksheetPart.AddHyperlinkRelationship(new System.Uri(anchor.Href, System.UriKind.Absolute), true, hyperLinkId);
Hyperlinks.Append(new Hyperlink()
{
Reference = cellReference, Id = hyperLinkId
});
// The entire cell will be a hyperlink but only the anchor text will look like a link
var valueWithoutAnchor = value.Replace(anchor.Value, string.Empty).TrimEnd('.').TrimEnd(Environment.NewLine.ToCharArray());
Run run1 = new Run();
RunProperties runProperties1 = new RunProperties();
FontSize fontSize3 = new FontSize()
{
Val = 11D
};
Color color3 = new Color()
{
Theme = (UInt32Value)1U
};
RunFont runFont1 = new RunFont()
{
Val = "Calibri"
};
FontFamily fontFamily1 = new FontFamily()
{
Val = 2
};
FontScheme fontScheme4 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
runProperties1.Append(fontSize3);
runProperties1.Append(color3);
runProperties1.Append(runFont1);
runProperties1.Append(fontFamily1);
runProperties1.Append(fontScheme4);
Text text1 = new Text();
text1.Text = valueWithoutAnchor;
text1.Space = SpaceProcessingModeValues.Preserve;
run1.Append(runProperties1);
run1.Append(text1);
Run run2 = new Run();
RunProperties runProperties2 = new RunProperties();
Underline underline2 = new Underline();
FontSize fontSize4 = new FontSize()
{
Val = 11D
};
Color color4 = new Color()
{
Theme = (UInt32Value)10U
};
RunFont runFont2 = new RunFont()
{
Val = "Calibri"
};
FontFamily fontFamily2 = new FontFamily()
{
Val = 2
};
FontScheme fontScheme5 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
runProperties2.Append(underline2);
runProperties2.Append(fontSize4);
runProperties2.Append(color4);
runProperties2.Append(runFont2);
runProperties2.Append(fontFamily2);
runProperties2.Append(fontScheme5);
Text text2 = new Text()
{
Space = SpaceProcessingModeValues.Preserve
};
text2.Text = string.Concat(anchor.Text, ".");
run2.Append(runProperties2);
run2.Append(text2);
returnObject.Append(run1);
returnObject.Append(run2);
}
return(returnObject);
}
// Generates content of worksheetPart1.
private static void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1, DataTable dtSource, bool encloseInDataTable)
{
Worksheet worksheet1 = new Worksheet();
worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
SheetData sheetData1 = new SheetData();
// todo: refactor to use bulk xml insert if performance is slow due to large data sets
Row row1 = new Row()
{
RowIndex = 1U
};
Cell cell1;
InlineString inlineString1;
Text text1;
CellValue cv;
// add header row
char charCol = 'A';
foreach (DataColumn dc in dtSource.Columns)
{
cell1 = new Cell()
{
CellReference = charCol.ToString() + "1", DataType = CellValues.InlineString
};
inlineString1 = new InlineString();
text1 = new Text();
text1.Text = dc.ColumnName;
inlineString1.Append(text1);
cell1.Append(inlineString1);
row1.Append(cell1);
charCol = (char)((int)charCol + 1);
}
sheetData1.Append(row1);
// add rows
int rowIx = 2;
foreach (DataRow dr in dtSource.Rows)
{
row1 = new Row()
{
RowIndex = (uint)rowIx
};
charCol = 'A';
foreach (DataColumn dc in dtSource.Columns)
{
if (dc.DataType == typeof(int) || dc.DataType == typeof(decimal))
{
cell1 = new Cell()
{
CellReference = charCol.ToString() + rowIx.ToString()
};
}
else
{
cell1 = new Cell()
{
CellReference = charCol.ToString() + rowIx.ToString(), DataType = CellValues.String
};
}
cv = new CellValue();
cv.Text = dr[dc.ColumnName].ToString();
cell1.Append(cv);
row1.Append(cell1);
charCol = (char)((int)charCol + 1);
}
sheetData1.Append(row1);
rowIx++;
}
worksheet1.Append(sheetData1);
string tableReferenceId = "rId2";
if (encloseInDataTable)
{
TableParts tableParts1 = new TableParts()
{
Count = (UInt32Value)1U
};
TablePart tablePart1 = new TablePart()
{
Id = tableReferenceId
};
tableParts1.Append(tablePart1);
worksheet1.Append(tableParts1);
}
worksheetPart1.Worksheet = worksheet1;
if (encloseInDataTable)
{
TableDefinitionPart tableDefinitionPart1 = worksheetPart1.AddNewPart <TableDefinitionPart>(tableReferenceId);
GenerateTableDefinitionPart1Content(tableDefinitionPart1, dtSource);
}
} // generate content
// Create a text cell
private Cell CreateTextCell(string header, UInt32 index, string text)
{
var cell = new Cell();
cell.CellReference = header + index;
cell.DataType = CellValues.InlineString;
var istring = new InlineString();
var cellText = new Text { Text = text };
istring.Append(cellText);
cell.Append(istring);
return cell;
}
static void Main(string[] args)
{
if (args.Count() != 1)
{
Console.WriteLine("Usage: {0} <path>", Environment.GetCommandLineArgs()[0]);
return;
}
try
{
foreach (string fileName in Directory.GetFiles(args[0], "*.xlsx"))
{
Console.WriteLine("Adding top row to worksheet \"Input\" for Excel file \"{0}\"...", fileName);
using (SpreadsheetDocument ssd = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart wbp = ssd.WorkbookPart;
Sheet sheet = wbp.Workbook.Descendants<Sheet>().Where(s => s.Name == "Input").FirstOrDefault();
WorksheetPart wsp = (WorksheetPart)ssd.WorkbookPart.GetPartById(sheet.Id.Value);
SheetData sd = wsp.Worksheet.GetFirstChild<SheetData>();
Row rr = sd.Descendants<Row>().Where(r => r.RowIndex == 2).FirstOrDefault();
Row nr = new Row() { RowIndex = 2 };
Cell a2 = new Cell() { CellReference = "A2", DataType = CellValues.InlineString };
{
InlineString ils = new InlineString();
ils.Append(new Text() { Text = "TypeGuessRows" });
a2.Append(ils);
}
Cell b2 = new Cell() { CellReference = "B2", DataType = CellValues.InlineString };
{
InlineString ils = new InlineString();
ils.Append(new Text() { Text = "0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789" });
b2.Append(ils);
}
nr.Append(a2);
nr.Append(b2);
CalculationChainPart ccp = wbp.CalculationChainPart;
if (ccp != null)
{
CalculationCell cc = ccp.CalculationChain.Descendants<CalculationCell>().Where(c => c.CellReference == "B2").FirstOrDefault();
if (cc != null)
cc.Remove();
if (ccp.CalculationChain.Count() == 0)
wbp.DeletePart(ccp);
}
foreach (Row rw in wsp.Worksheet.Descendants<Row>().Where(r => r.RowIndex.Value >= 2))
{
uint nri = Convert.ToUInt32(rw.RowIndex.Value + 1);
foreach (Cell cl in rw.Elements<Cell>())
{
string cr = cl.CellReference.Value;
cl.CellReference = new StringValue(cr.Replace(rw.RowIndex.Value.ToString(), nri.ToString()));
}
rw.RowIndex = new UInt32Value(nri);
}
sd.InsertBefore(nr, rr);
wsp.Worksheet.Save();
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Excel.Application excel = new Excel.Application();
//Excel.Workbook workbook = excel.Workbooks.Open(
// @"C:\DATA\NS_IMPORT\Network Standards - Managing Risk - Canada.xlsx",
// 0,
// false,
// 5,
// String.Empty,
// String.Empty,
// true,
// Excel.XlPlatform.xlWindows,
// "\t",
// true,
// false,
// 0,
// false,
// true,
// false);
//Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.get_Item("Input");
//worksheet.Rows.Insert(2, 1);
//worksheet.Rows[2][0].Value = "TYPESELECTOR";
//worksheet.Rows[2][1].Value = "0123456789";
//excel.SaveWorkspace(@"C:\DATA\NS_IMPORT\OUTPUT.xlsx");
}
catch (Exception ex)
{
while (ex != null)
{
Console.WriteLine(ex.Message);
ex = ex.InnerException;
}
}
//Console.WriteLine("\nPress <any key> to continue.");
//Console.ReadKey(true);
}