public static Cell createTextCell(
int columnIndex,
int rowIndex,
object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
DocumentFormat.OpenXml.Spreadsheet.Text t = new DocumentFormat.OpenXml.Spreadsheet.Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
private Cell CreateCell(string header, UInt32 index, string text)
{
Cell cell;
double number;
if (double.TryParse(text, out number))
{
cell = new Cell
{
DataType = CellValues.Number,
CellReference = header + index,
CellValue = new CellValue(number.ToString(CultureInfo.InvariantCulture))
};
}
else
{
cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = header + index
};
var istring = new InlineString();
var t = new Text {
Text = text
};
istring.AppendChild(t);
cell.AppendChild(istring);
}
return(cell);
}
private Cell CreateTextCell(string header, uint index, string text)
{
var cell = new Cell
{
CellReference = header + index,
};
// try to convert value to int to set up correct cell format
// if text can be converted to int but starts from zeros, it will be pasted as string in order to save zeros
int intValue;
if (int.TryParse(text, out intValue) && text == intValue.ToString())
{
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
cell.CellValue = new CellValue(text);
}
else
{
cell.DataType = CellValues.InlineString;
var istring = new InlineString();
var t = new Text {
Text = text
};
istring.AppendChild(t);
cell.AppendChild(istring);
}
return(cell);
}
private static Row CreateNewRow(int rowIndex, params string[] data)
{
// New Row
Row row = new Row { RowIndex = (UInt32)rowIndex };
for (int i = 0; i < data.Length; i++)
{
// A = 65 for the first column, B = 66, C = 67...
string column = ((char) (65 + i)).ToString();
// New Cell
Cell cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = column + rowIndex
};
// Create Text object
Text t = new Text {Text = data[i]};
// Append Text to InlineString object
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
// Append InlineString to Cell
cell.AppendChild(inlineString);
// Append Cell to Row
row.AppendChild(cell);
}
return row;
}
//gavdcodeend 01
//gavdcodebegin 02
public static void ExcelOpenXmlInsertTextInCell()
{
using (SpreadsheetDocument myExcelDoc =
SpreadsheetDocument.Open(@"C:\Temporary\ExcelDoc01.xlsx", true))
{
WorkbookPart myWorkbookPart = myExcelDoc.WorkbookPart;
WorksheetPart myWorksheetPart = myWorkbookPart.WorksheetParts.First();
SheetData mySheetData = myWorksheetPart.Worksheet.Elements <SheetData>().
First();
Row newRow = mySheetData.Elements <Row>().FirstOrDefault();
Cell newCell = new Cell()
{
DataType = CellValues.InlineString,
CellReference = "C3"
};
InlineString myInlineString = new InlineString();
Text myText = new Text()
{
Text = "Text in Cell"
};
myInlineString.AppendChild(myText);
newCell.AppendChild(myInlineString);
newRow.Append(newCell);
}
}
protected virtual Cell CreateTextCell(string header, UInt32 index, string text, string formula)
{
var cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = header + index
};
if (formula?.Length > 0)
{
var cellFormula1 = new CellFormula {
Text = formula
};
var cellValue1 = new CellValue {
Text = text
};
cell.Append(cellFormula1);
cell.Append(cellValue1);
}
else
{
var istring = new InlineString();
var t = new Text {
Text = text
};
istring.AppendChild(t);
cell.AppendChild(istring);
}
return(cell);
}
/// <summary>
/// Converts the object to cell.
/// </summary>
/// <param name="value">The value.</param>
/// <returns></returns>
private static Cell ConvertObjectToCell(object value)
{
var objType = value.GetType();
var cell = new Cell();
if (objType == typeof(decimal))
{
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(value.ToString());
}
else if (objType == typeof(int))
{
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(value.ToString());
}
else
{
cell.DataType = CellValues.InlineString;
var inlineString = new InlineString();
var text = new Text {
Text = value.ToString()
};
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
return(cell);
}
/// <summary>
/// This method is used to create the Row to append in excel
/// </summary>
/// <param name="rIndex"></param>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <returns></returns>
private static Row GetRowToAppendInExcel(SpreadsheetDocument document, WorksheetPart worksheetPart, int rIndex, List <ExcelCellValues> columnValueList)
{
Row r = new Row();
try
{
r.RowIndex = (UInt32)rIndex;
foreach (var colValue in columnValueList)
{
if (!string.IsNullOrEmpty(colValue.CellUpdateValue))
{
Cell cell = new Cell();
cell.CellReference = colValue.CellName + Convert.ToString(rIndex);
cell.DataType = CellValues.String;
InlineString inlinefString = new InlineString();
Text txt = new Text();
txt.Text = colValue.CellUpdateValue;
inlinefString.AppendChild(txt);
cell.AppendChild(inlinefString);
r.AppendChild(cell);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(r);
}
private Cell CreateContentCell(string header, UInt32 index, string text, CellValues contentType)
{
Cell cell = new Cell
{
DataType = contentType,
CellReference = header + index
};
if (contentType != CellValues.Number)
{
InlineString istring = new InlineString();
Text t = new Text {
Text = text
};
istring.AppendChild(t);
cell.AppendChild(istring);
}
else
{
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(text);
}
return(cell);
}
private static Cell CreateTextCell(string header, UInt32 index, string text, uint styleIndex = 0)
{
long result = 0;
Cell cell = null;
if (long.TryParse(text, out result))
{
cell = new Cell
{
DataType = CellValues.Number,
CellReference = header + index,
StyleIndex = styleIndex
};
}
else
{
cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = header + index,
StyleIndex = styleIndex
};
}
var istring = new InlineString();
var t = new Text {
Text = text
};
istring.AppendChild(t);
cell.AppendChild(istring);
return(cell);
}
/// <summary>
/// Creates a new Cell object with the InlineString data type.
/// </summary>
private static Cell CreateTextCell(
object cellValue,
uint?styleIndex, bool isRelationshipValue = false)
{
var cell = new Cell
{
DataType = CellValues.InlineString
};
//apply the cell style if supplied
if (styleIndex.HasValue)
{
cell.StyleIndex = styleIndex.Value;
}
var inlineString = new InlineString( );
var t = new DocumentFormat.OpenXml.Spreadsheet.Text( );
if (cellValue != null)
{
t.Text = isRelationshipValue ? DatabaseTypeHelper.GetEntityXmlName(cellValue.ToString( )) : cellValue.ToString( );
}
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return(cell);
}
private static void overwriteTextOfTextCell(Cell cell, string newCellText)
{
Text t = new Text();
t.Text = newCellText;
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
cell.DataType = CellValues.InlineString;
int count = cell.ChildElements.Count();
if (count > 0)
{
if (count > 1)
{
Console.WriteLine("WARNING! - Overwriting cell with {0:D} children", count);
}
//Console.WriteLine("replacing inlinstring child");
cell.ReplaceChild(inlineString, cell.GetFirstChild <InlineString>());
}
else
{
cell.AppendChild(inlineString);
}
}
public static Row CreateRowValues(int index, DataInSheet objToInsert)
{
Row row = new Row();
row.RowIndex = (UInt32)index;
int i = 0;
foreach (var property in objToInsert.GetType().GetProperties())
{
Cell cell = new Cell();
cell.CellReference = headerColumns[i].ToString() + index;
if (property.PropertyType.ToString().Equals("System.string", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "";
}
cell.DataType = CellValues.String;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
if (property.PropertyType.ToString().Equals("System.int32", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = 0;
}
CellValue cellValue = new CellValue();
cellValue.Text = result.ToString();
cell.AppendChild(cellValue);
}
if (property.PropertyType.ToString().Equals("System.boolean", StringComparison.InvariantCultureIgnoreCase))
{
var result = property.GetValue(objToInsert, null);
if (result == null)
{
result = "False";
}
cell.DataType = CellValues.InlineString;
InlineString inlineString = new InlineString();
Text text = new Text();
text.Text = result.ToString();
inlineString.AppendChild(text);
cell.AppendChild(inlineString);
}
row.AppendChild(cell);
i = i + 1;
}
return(row);
}
public static byte[] UpdateCells(MemoryStream doc, string title, List <string> headers, List <List <string> > rows)
{
// Open the document for editing.
SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(doc, true);
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "Fax-ArchiveReport");
if (worksheetPart != null)
{
//----------------------------------------------- title
Cell cellTitle = GetCell(worksheetPart.Worksheet, ColumnsNames[0], 2);
var inlineStringTitle = new InlineString();
inlineStringTitle.AppendChild(new Text {
Text = title ?? string.Empty
});
cellTitle.AppendChild(inlineStringTitle);
cellTitle.DataType = new EnumValue <CellValues>(CellValues.InlineString);
//----------------------------------------------- data
uint rowIndex = 4;
foreach (var row in rows)
{
uint rowCellIndex = 0;
foreach (var rowCell in row)
{
Cell cell = GetCell(worksheetPart.Worksheet, ColumnsNames[rowCellIndex], rowIndex);
var inlineStringRowCell = new InlineString();
inlineStringRowCell.AppendChild(new Text {
Text = rowCell ?? string.Empty
});
cell.AppendChild(inlineStringRowCell);
cell.DataType = new EnumValue <CellValues>(CellValues.InlineString);
rowCellIndex++;
}
rowIndex++;
}
//-----------------------------------------------
worksheetPart.Worksheet.Save();
spreadSheet.Close();
var bytes = doc.ToArray();
doc.Dispose();
return(bytes);
}
return(null);
}
//thaond11
private void UpdateCellTextInline(Cell cell, string cellValue)
{
InlineString inlineString = new InlineString();
Text cellValueText = new Text {
Text = cellValue
};
inlineString.AppendChild(cellValueText);
cell.DataType = CellValues.InlineString;
cell.AppendChild(inlineString);
}
//thaond11
public static Cell AddValueToCell(Cell cell, string text)
{
cell.DataType = CellValues.InlineString;
cell.RemoveAllChildren();
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = text;
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return(cell);
}
private Cell GetCell(string text)
{
var cell = new Cell()
{
DataType = CellValues.InlineString
};
var inlineString = new InlineString();
inlineString.AppendChild(new Text(text));
cell.AppendChild(inlineString);
return(cell);
}
private static Cell CreateInlineStringCell(string text)
{
Cell c = new Cell();
c.DataType = CellValues.InlineString;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = text;
inlineString.AppendChild(t);
c.AppendChild(inlineString);
return(c);
}
private Cell _CreateTextCell(Cell cell, string text)
{
cell.DataType = CellValues.InlineString;
var iStr = new InlineString();
var t = new DocumentFormat.OpenXml.Spreadsheet.Text {
Text = text
};
iStr.AppendChild(t);
cell.AppendChild(iStr);
return(cell);
}
private Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
var cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = getColumnName(columnIndex) + rowIndex.ToString();
var inlineString = new InlineString();
var t = new System.Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return(cell);
}
private Cell CreateTextCell(string header, UInt32 index, string text)
{
var cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = header + index
};
var istring = new InlineString();
var t = new Text { Text = text };
istring.AppendChild(t);
cell.AppendChild(istring);
return cell;
}
private static Cell createTextCell(int columnIndex, int rowIndex, string cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
//cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text(cellValue);
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
private static void OutputTemplatedData(object[][] inputData, SpreadsheetDocument ssDoc, int templateCellsRow, string sheetName)
{
WorkbookPart workbookPart = ssDoc.WorkbookPart;
Sheet worksheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(worksheet.Id);
Row tRow = wsPart.Worksheet.Descendants<Row>().
Where(r => r.RowIndex == templateCellsRow).FirstOrDefault();
int cnt = tRow.Count();
List<Cell> cellLookup = new List<Cell>();
for (int i = 0; i < cnt; i++)
{
Cell c = (Cell)tRow.ElementAt(i);
cellLookup.Add(c);
}
//var styles = workbookPart.GetPartsOfType<WorkbookStylesPart>().FirstOrDefault();
SheetData sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();
int currRowIndex = templateCellsRow + 1;
for (int i = 0; i < inputData.GetLength(0); i++)
{
Row newRow = new Row();
newRow.RowIndex = (UInt32)(currRowIndex);
for (int j = 0; j < inputData[i].Count(); j++)
{
Cell newCell = (Cell)cellLookup[j].CloneNode(true);
newCell.CellReference = ExcelColumnReferenceLookup[j] + currRowIndex;
//newCell.DataType = CellValues.SharedString;
//int ssIndex = ExcelReportGenerator.InsertSharedStringItem(workbookPart, inputData[i][j].ToString());
//newCell.CellValue.Text = ssIndex.ToString();
newCell.DataType = CellValues.InlineString;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = inputData[i][j].ToString();
inlineString.AppendChild(t);
newCell.AppendChild(inlineString);
newRow.AppendChild(newCell);
}
sheetData.AppendChild(newRow);
currRowIndex++;
}
}
/// <summary>
/// Gets the cell with text.
/// </summary>
/// <param name="text">The <see cref="string" /></param>
/// <returns>
/// The <see cref="Cell" />
/// </returns>
private Cell GetCellWithText(string text)
{
Cell c1 = new Cell();
c1.DataType = CellValues.InlineString;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = text;
inlineString.AppendChild(t);
c1.AppendChild(inlineString);
return(c1);
}
//thaond11
public static Cell CreateTextCell(string header, string text, int index)
{
//Create new inline string cell
Cell c = new Cell();
c.DataType = CellValues.InlineString;
//Add text to text cell
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = text;
inlineString.AppendChild(t);
c.AppendChild(inlineString);
return(c);
}
private static Cell createTextCell(int columnIndex, int rowIndex, string cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
//cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text(cellValue);
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return(cell);
}
private static Cell AddCellWithText(string text)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
InlineString inlineString = new InlineString();
Text t = new Text(text);
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return(cell);
}
// ReSharper disable once MemberCanBeMadeStatic.Local
private Cell ToInlineStringCell(string value)
{
var cell = new Cell {
DataType = CellValues.InlineString
};
// Excel has a limit of 32767 characters per cell.
// See https://support.office.com/en-sg/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa
var inlineString = new InlineString();
inlineString.AppendChild(new Text {
Text = value.Substring(0, Math.Min(value.Length, 32764))
});
cell.AppendChild(inlineString);
return(cell);
}
public static Cell CreateTextCell(string text)
{
var cell = new Cell
{
DataType = CellValues.InlineString,
};
var istring = new InlineString();
var t = new Text {
Text = text
};
istring.AppendChild(t);
cell.AppendChild(istring);
return(cell);
}
private Cell CreateTextCell(string header, UInt32 index, string text)
{
var cell = new Cell
{
DataType = CellValues.InlineString,
CellReference = header + index
};
var istring = new InlineString();
var t = new Text {
Text = text
};
istring.AppendChild(t);
cell.AppendChild(istring);
return(cell);
}
private static Cell CreateInlineTextCell(string data, string cellReference)
{
Cell newCell = new Cell();
newCell.DataType = CellValues.InlineString;
newCell.CellReference = cellReference;
InlineString inlineString = new InlineString();
Text t = new Text();
// t.Text = data;
t.Text = HttpUtility.HtmlDecode(data);
inlineString.AppendChild(t);
newCell.AppendChild(inlineString);
return newCell;
}
//Method for when the datatype is text based
public Cell CreateTextCell(string header, string text, int index)
{
//Create a new inline string cell.
Cell c = new Cell();
c.DataType = CellValues.InlineString;
c.CellReference = header + index;
//Add text to the text cell.
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = text;
inlineString.AppendChild(t);
c.AppendChild(inlineString);
return(c);
}
private static Cell CreateCell(int columnNumber, string value, int index)
{
Cell formattedCell = new Cell();
formattedCell.DataType = CellValues.InlineString;
formattedCell.CellReference = GetExcelColumnName(columnNumber) + index;
//formattedCell.StyleIndex = 4
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = value;
inlineString.AppendChild(t);
formattedCell.AppendChild(inlineString);
//End If
return(formattedCell);
}
/// <summary>
/// This method creates text cell
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="cellValue"></param>
/// <returns></returns>
private static Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Excel.Text t = new Excel.Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return(cell);
}
private Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
private void button2_Click(object sender, EventArgs e)
{
string src = @"template/2.xlsx";
//OpenXML SDK 2.5
//REF: http://msdn.microsoft.com/en-us/library/office/cc850837.aspx
string dst = src.Replace(Path.GetFileName(src), "Astro2_Sys WHCK Status - 0712-SA2.xlsx"); //另存目的檔
File.Copy(src, dst, true);
using (var shtDoc = SpreadsheetDocument.Open(dst, true))
{
//var sht = shtDoc.WorkbookPart.Workbook.Descendants<Sheet>().First();
var sht = shtDoc.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Astro 2 TestResult 9431").FirstOrDefault();
var shtPart = shtDoc.WorkbookPart.GetPartById(sht.Id) as WorksheetPart;
//var cell = shtPart.Worksheet.Descendants<Row>().First().Descendants<Cell>().First();
// 3. 建立 Cell 物件，設定寫入位置，格式，資料
Cell cell = InsertCellInWorksheet("G", 8, shtPart);
//REF: InlineString http://bit.ly/ZpUf18
var ins = new InlineString();
ins.AppendChild(new Text("Failed"));
cell.AppendChild(ins);
cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.InlineString);
shtPart.Worksheet.Save();
shtDoc.WorkbookPart.Workbook.Save();
shtDoc.Close();
}
}
protected Row buildRowFromPhoneLog(int index, Models.PhoneLog log)
{
// New Row
Row row = new Row();
row.RowIndex = (UInt32)index;
// New Cell
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
// Column A1, 2, 3 ... and so on
//cell.CellReference = "A" + index;
Text t = new Text();
t.Text = log.Id.ToString();
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
row.AppendChild(cell);
cell = new Cell();
cell.DataType = CellValues.InlineString;
t = new Text();
t.Text = log.CallerName;
// Append Text to InlineString object
inlineString = new InlineString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
row.AppendChild(cell);
cell = new Cell();
cell.DataType = CellValues.InlineString;
t = new Text();
t.Text = log.PhoneNumber;
// Append Text to InlineString object
inlineString = new InlineString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
row.AppendChild(cell);
cell = new Cell();
cell.DataType = CellValues.InlineString;
t = new Text();
t.Text = log.CallType;
// Append Text to InlineString object
inlineString = new InlineString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
row.AppendChild(cell);
cell = new Cell();
cell.DataType = CellValues.InlineString;
t = new Text();
t.Text = log.Message;
// Append Text to InlineString object
inlineString = new InlineString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
row.AppendChild(cell);
cell = new Cell();
cell.DataType = CellValues.InlineString;
t = new Text();
t.Text = log.EmployeeEmail;
// Append Text to InlineString object
inlineString = new InlineString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
row.AppendChild(cell);
cell = new Cell();
cell.DataType = CellValues.InlineString;
t = new Text();
t.Text = log.CallDate.Value.ToShortDateString();
// Append Text to InlineString object
inlineString = new InlineString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
row.AppendChild(cell);
cell = new Cell();
cell.DataType = CellValues.InlineString;
t = new Text();
t.Text = log.FollowedUp.Value.ToString();
// Append Text to InlineString object
inlineString = new InlineString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
row.AppendChild(cell);
return row;
}
/// <summary>
/// Add cell into the passed row
/// </summary>
/// <param name="row">The row to add a cell to</param>
/// <param name="rowIndex">The index of the row</param>
/// <param name="value">The value for the cell</param>
public void AppendCell(Row row, uint rowIndex, string value)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
Text t = new Text();
t.Text = value;
//Append the Text
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
//Append to cell
cell.AppendChild(inlineString);
// Get the last cell's column
string nextCol = "A";
Cell c = (Cell) row.LastChild;
if (c != null) // if cells exist
{
int numIndex = c.CellReference.ToString().IndexOfAny(new char[] {'1', '2', '3', '4', '5', '6', '7', '8', '9'});
//Get the last column ref
string lastCol = c.CellReference.ToString().Substring(0, numIndex);
nextCol = IncrementColRef(lastCol);
}
cell.CellReference = nextCol + rowIndex;
row.AppendChild(cell);
}
private Cell CreateTextCell(string header, string text, int index)
{
// New Cell
Cell cell = new Cell { DataType = CellValues.InlineString, CellReference = header + index };
// Create Text object
Text t = new Text { Text = text };
// Append Text to InlineString object
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
// Append InlineString to Cell
cell.AppendChild(inlineString);
return cell;
}
public void GenerateExcel(DataTable YoutdTName, string YourExcelfileName)
{
// Create cell reference array
string[] CellReferenceArray = new string[] { "A", "B", "C", "D", "E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W" };
//Open your saved excel file that you have created using template file.
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(YourExcelfileName, true))
{
// Get Workbook Part of Spread Sheet Document
WorkbookPart objworkbook = spreadsheetDocument.WorkbookPart;
// Get all sheets in spread sheet document
IEnumerable<Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
// Get relationship Id
string relationshipId = sheetcollection.First().Id.Value;
// Get sheet1 Part of Spread Sheet Document
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet worksheet = worksheetPart.Worksheet;
// Get Data in Excel file
SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
IEnumerable<Row> rowcollection = sheetData.Descendants<Row>().Skip(1);
if (rowcollection.Count() == 0)
{
return;
}
// Create style sheet object that will be used for applying styling.
// Stylesheet objstyleSheet = objworkbook.WorkbookStylesPart.Stylesheet;
int i = 1;
UInt32 index = 11;
foreach (DataRow dr in YoutdTName.Rows)
{
i = 0;
foreach (DataColumn col in YoutdTName.Columns)
{
if (i < 23)
{
Cell theCell = GetCell(worksheet, CellReferenceArray[i], index);
if (theCell != null && string.IsNullOrEmpty(GetCellValue(objworkbook,theCell)))
{
//CellValue v1 = new CellValue(dr[col].ToString());
//theCell.CellValue = v1;
theCell.DataType = CellValues.InlineString;
theCell.RemoveAllChildren();
InlineString inline = new InlineString();
Text t = new Text();
t.Text = dr[col].ToString();
inline.AppendChild(t);
theCell.AppendChild(inline);
worksheetPart.Worksheet.Save();
}
i += 1;
}
}
index += 1;
}
}
}
public static bool SetDataToExcel(string pathToExcelFile, string sheetName, DataTable data, string[] intro, string title)
{
try
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook wb;
if (!System.IO.File.Exists(pathToExcelFile))
{
//15/03/2011 Template ?
System.IO.File.Copy(SystemHelper.GetConfigValue("appStartupPath") + "\\template\\newWorkbook.xlsx", pathToExcelFile);
}
wb = app.Workbooks.Open(pathToExcelFile, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Find sheetname
int index = 1;
for (index = 1; index <= wb.Sheets.Count; index++)
{
if (((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Name.ToLower().Trim() == sheetName.ToLower().Trim())
break;
}
if (index > wb.Sheets.Count)
{
wb.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[index]).Activate();
}
Microsoft.Office.Interop.Excel.Worksheet activeSheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
activeSheet.Name = sheetName;
//activeSheet.Cells.FormatConditions =
if (!System.IO.File.Exists(pathToExcelFile))
{
//wb.SaveAs(pathToExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveAs(pathToExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
wb.Save();
}
// exit excel, still now work so far
wb.Close(true, Type.Missing, Type.Missing);
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app.Workbooks);
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(app) != 0)
{ };
//System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Close();
//System.Diagnostics.Process.GetProcessById(app.Windows.Application.Hwnd).Kill();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(pathToExcelFile, true))
{
IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0) // the sheet with that name couldn't be found
{
return false;
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
//sheetData.RemoveAllChildren<Row>();
//SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;
int lastRowIndex = sheetData.ChildElements.Count;
int k = 0; //step in intro
for (int i = lastRowIndex; i < data.Rows.Count + 4 + lastRowIndex + intro.Length; i++) // +1 for the header label, + 2 for spacing row, + 1 for title
{
DataRow dataRow = null;
if (i > lastRowIndex + 3 + intro.Length)
{
dataRow = data.Rows[i - 4 - lastRowIndex - intro.Length];
}
Row aRow = new Row();
aRow.RowIndex = (UInt32)i + 1;
bool isRowEnd = false;//Fix the excel expand
for (int j = 0; j < data.Columns.Count; j++)
{
if (isRowEnd)
{
break;//Fix the excel expand
}
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = GetAlpha(j + 1) + (i + 1);
InlineString inlineString = new InlineString();
Text t = new Text();
if (i <= lastRowIndex + intro.Length - 1)//Intro
{
if (j == 0)
{
t.Text = intro[k];
k++;
cell.StyleIndex = (UInt32Value)3U;
isRowEnd = true;
}
}
else if (i == lastRowIndex + intro.Length + 1)//Title
{
if (j == 0)
{
t.Text = title;
cell.StyleIndex = (UInt32Value)4U;
isRowEnd = true;
}
}
else if (i == lastRowIndex + 3 + intro.Length)//Header
{
t.Text = data.Columns[j].ToString();
cell.StyleIndex = (UInt32Value)1U;
}
else
{
if (i != intro.Length + lastRowIndex && i != intro.Length + lastRowIndex + 2)//Null spacing row
t.Text = dataRow[j].ToString();//Data row
}
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
aRow.AppendChild(cell);
}
sheetData.AppendChild(aRow);
}
System.Xml.XmlWriter test = System.Xml.XmlWriter.Create("Test.xml");
worksheetPart.Worksheet.WriteTo(test);
test.Close();
worksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();
document.Close();
}
}
catch (Exception e)
{
SystemHelper.LogEntry(string.Format("Error occurs on method {0} - Message {1}", "GetDataFromExcel", e.Message));
return false;
}
return true;
}
Cell CreateTextCell(string header, string text, int index)
{
//Create a new inline string cell.
Cell c = new Cell();
c.DataType = CellValues.InlineString;
c.CellReference = header + index;
//Add text to the text cell.
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = text;
inlineString.AppendChild(t);
c.AppendChild(inlineString);
return c;
}
/// <summary>
/// Using openxml
/// </summary>
private void ExportToExcel()
{
// Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(ExportFileLocation + ddlTables.SelectedItem.ToString() + ".xlsx", true))
{
// Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
// Get the first worksheet.
//WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt(2);
WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt(0);
// The SheetData object will contain all the data.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
// Begining Row pointer
int index = 2;
Row row = new Row();
row.RowIndex = (UInt32)1;
#region Making headers
for (int i = 0; i < dt.Columns.Count; i++)
{
// New Cell
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
// Column A1, 2, 3 ... and so on
cell.CellReference = Convert.ToChar(65 + i).ToString() + "1";
// Create Text object
Text t = new Text();
t.Text = dt.Columns[i].ColumnName;
// Append Text to InlineString object
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
// Append InlineString to Cell
cell.AppendChild(inlineString);
// Append Cell to Row
row.AppendChild(cell);
}
// Append Row to SheetData
sheetData.AppendChild(row);
#endregion
// For each item in the database, add a Row to SheetData.
foreach (DataRow dr in dt.Rows)
{
// New Row
row = new Row();
row.RowIndex = (UInt32)index;
for (int i = 0; i < dt.Columns.Count; i++)
{
// New Cell
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
// Column A1, 2, 3 ... and so on
cell.CellReference = Convert.ToChar(65 + i).ToString() + index;
// Create Text object
Text t = new Text();
t.Text = dr[i].ToString();
// Append Text to InlineString object
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
// Append InlineString to Cell
cell.AppendChild(inlineString);
// Append Cell to Row
row.AppendChild(cell);
}
// Append Row to SheetData
sheetData.AppendChild(row);
// increase row pointer
index++;
}
// save
worksheetPart.Worksheet.Save();
myWorkbook.Dispose();
}
}