public void CreatePreserveCell(ExcelCell cell, IXlsxDocumentBuilder document)
{
string _PreservationSheetName_ = "PreservationSheet";
var preservationSheet = document.GetPreservationSheet() as XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder;
if (preservationSheet == null)
{
preservationSheet = new XlsxExportImport.Base.Builders.SqadXlsxSheetBuilder(_PreservationSheetName_, false, true, true);
document.AppendSheet(preservationSheet);
}
else
{
preservationSheet.AddAndActivateNewTable(_PreservationSheetName_);
}
preservationSheet.AppendColumnHeaderRowItem(cell.CellHeader);
Dictionary <string, object> resolveRow = new Dictionary <string, object>();
resolveRow.Add(cell.CellHeader, cell.CellValue);
PopulateRows(resolveRow.Keys.ToList(), resolveRow, preservationSheet);
}
public void As_converts_cell_value_type_to_generic_argument_type()
{
var newCell = new ExcelCell("2");
Assert.AreEqual(2, newCell.Cast <int>());
Assert.AreEqual(typeof(int), newCell.Cast <int>().GetType());
}
private static ExcelCellMatrix ProcessListToCellMatrix(ProcessList processList)
{
var numRows = 1 + processList.Processes.Count();
var c = 0;
ExcelCell[][] cells = new ExcelCell[numRows][];
var initR = 0;
var r = initR;
foreach (var process in processList.Processes)
{
cells[r] = new ExcelCell[c + 1];
var macro = string.Format(@"=EXEC(""{0}"")", EscapeString(process.CmdLine.ToString()));
if (r == initR)
{
cells[r][c] = new ExcelCell(value: macro, name: "Auto_open");
}
else
{
cells[r][c] = new ExcelCell(value: macro);
}
r++;
}
cells[r] = new ExcelCell[c + 1];
cells[r][c] = new ExcelCell(value: "=HALT()");
return(new ExcelCellMatrix(cells));
}
//创建一个空的表结构
private DataTable createDataTable()
{
ExcelWorksheet sheet = _ExcelObj.Worksheets.ActiveWorksheet;
if (sheet.Rows.Count == 0)
{
return(null);
}
ExcelRow headRow = sheet.Rows[0];
DataTable newDt = new DataTable();
int aCount = headRow.AllocatedCells.Count;
for (int i = 0; i < aCount; i++)
{
ExcelCell cell = headRow.AllocatedCells[i];
if (cell.Value == null)
{
continue;
}
DataColumn dc = new DataColumn(cell.Value.ToString().Trim());
dc.Caption = cell.Value.ToString().Trim();
_ColumnAndIndex.Add(i, dc);
newDt.Columns.Add(dc);
}
return(newDt);
}
public byte[] BuildExcelFile(int numberOfWorksheet, int numberOfColumns, int numberOfRows)
{
for (var worksheetIndex = 0; worksheetIndex < numberOfWorksheet; worksheetIndex++)
{
var worksheetName = $"worksheet_{worksheetIndex}";
var cells = new ExcelCell[numberOfColumns];
for (var columnIndex = 0; columnIndex < numberOfColumns; columnIndex++)
{
cells[columnIndex] = new ExcelCell($"cell_name_{columnIndex}", ExcelSheetStyleIndex.Bold);
}
_excelBuilder.AddRowToWorksheet(worksheetName, cells);
for (var rowIndex = 0; rowIndex < numberOfRows; rowIndex++)
{
cells = new ExcelCell[numberOfColumns];
for (var columnIndex = 0; columnIndex < numberOfColumns; columnIndex++)
{
cells[columnIndex] = new ExcelCell($"cell_value_{rowIndex}_{columnIndex}", ExcelSheetStyleIndex.Default);
}
_excelBuilder.AddRowToWorksheet(worksheetName, cells);
}
}
return(_excelBuilder.FinishAndGetExcel());
}
//根据Caption 找到datatable中的列,并把和Excel 中对应列的Index 一起存储在hashTable中作为
//获取行导入时对应的列。
private bool setColumnAndIndex(DataTable dt, ExcelFile excelObj, Hashtable columnAndIndex)
{
ExcelWorksheet sheet = excelObj.Worksheets.ActiveWorksheet;
if (sheet.Rows.Count == 0)
{
return(false);
}
ExcelRow headRow = sheet.Rows[0];
int aCount = headRow.AllocatedCells.Count;
for (int i = 0; i < aCount; i++)
{
ExcelCell cell = headRow.AllocatedCells[i];
if (cell.Value == null)
{
continue;
}
foreach (DataColumn dc in dt.Columns)
{
if (string.Compare(dc.Caption, cell.Value.ToString(), true) != 0)
{
continue;
}
columnAndIndex.Add(i, dc);
break;
}
}
return(columnAndIndex.Count > 0);
}
public DataTable GetSpreadsheet(string sheetName, ExcelCell startingCell, ExcelCell endingCell)
{
if (string.IsNullOrEmpty(sheetName))
{
throw new ArgumentNullException("sheetName", "sheetName cannot be null or empty");
}
DataTable dtResult = new DataTable();
using (var connection = new OleDbConnection(GetConnectionString())) {
connection.Open();
if (!sheetName.EndsWith("$"))
{
sheetName += "$";
}
using (var command = new OleDbCommand("Select * From [" + sheetName + startingCell.Cell + ":" + endingCell.Cell + "]", connection)) {
using (var adapter = new OleDbDataAdapter(command)) {
using (DataSet ds = new DataSet()) {
adapter.Fill(ds);
if (ds.Tables.Count > 0)
{
dtResult = ds.Tables[0];
}
ds.Dispose();
}
}
}
connection.Close();
}
return(dtResult);
}
public static void writeFormattedText(string text, ExcelCell cell)
{
StringBuilder sb = new StringBuilder();
foreach (System.Text.RegularExpressions.Match pMatch in parameterHeaderRegex.Matches(text))
{
foreach (System.Text.RegularExpressions.Match aMatch in attributeRegex.Matches(pMatch.Value))
{
switch (aMatch.Groups["name"].Value)
{
case "text":
sb.Append(aMatch.Groups["value"].Value);
break;
//case "font":
// cell.Style.Font.Name = aMatch.Groups["value"].Value;
// break;
//case "index":
// cell.Style.Font.ScriptPosition = (aMatch.Groups["value"].Value == "sup") ? ScriptPosition.Superscript : ScriptPosition.Subscript;
// break;
case "style":
switch (aMatch.Groups["value"].Value)
{
case "underline": cell.Style.Font.UnderlineStyle = UnderlineStyle.Single; break;
case "bold": cell.Style.Font.Weight = 5; break;
case "italic": cell.Style.Font.Italic = true; break;
}
break;
}
}
}
cell.Value = sb.ToString();
}
private ExcelCell ParseExcelCell(ICell c, PropertyInfo info, ExcelRow row)
{
string content = c == null ? string.Empty : c.ToString();
ExcelCell cell = new ExcelCell(row, content, info);
return(cell);
}
private void cleanTable(string worksheetName)
{
while (true)
{
int i = 0;
for (; i < m_reportTable.WorkSheets[worksheetName].Count; ++i)
{
for (int x = 0; x < m_reportTable.WorkSheets[worksheetName][i].Count; ++x)
{
ExcelCell cell = m_reportTable.WorkSheets[worksheetName][i][x];
if (cell.Tag != null)
{
m_reportTable.WorkSheets[worksheetName][i].RemoveAt(x);
if (m_reportTable.WorkSheets[worksheetName][i].Count == 0)
{
m_reportTable.WorkSheets[worksheetName].RemoveAt(i);
m_reportTable.WorkSheets[worksheetName].ForceUpdateRowNumbers();
break;
}
}
}
}
if (i >= m_reportTable.WorkSheets[worksheetName].Count - 1)
{
break;
}
}
}
public static int QuickWrite(ExcelSheet excelSheet, string filePath, ExcelVersions version = ExcelVersions.Xls)
{
using (FileStream fs = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
IWorkbook workbook = OpenWrite(version);
ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(excelSheet.Name) ? "sheet1" : excelSheet.Name);
for (int i = 0; i < excelSheet.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i);
ExcelRow excelRow = excelSheet.Rows[i];
for (int j = 0; j < excelRow.Cells.Count; j++)
{
ExcelCell excelCell = excelRow.GetCell(j);
ICell cell = CreateCell(row, j, excelCell);
}
}
workbook.Write(fs);
fs.Close();
return(DotNETCode.SUCCESS);
}
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = ExcelFile.Load("NumberFormat.xlsx");
var ws = ef.Worksheets[0];
ws.Cells[0, 2].Value = "ExcelCell.Value";
ws.Columns[2].Style.NumberFormat = "@";
ws.Cells[0, 3].Value = "CellStyle.NumberFormat";
ws.Columns[3].Style.NumberFormat = "@";
ws.Cells[0, 4].Value = "ExcelCell.GetFormattedValue()";
ws.Columns[4].Style.NumberFormat = "@";
for (int i = 1; i < ws.Rows.Count; i++)
{
ExcelCell sourceCell = ws.Cells[i, 0];
ws.Cells[i, 2].Value = sourceCell.Value == null ? null : sourceCell.Value.ToString();
ws.Cells[i, 3].Value = sourceCell.Style.NumberFormat;
ws.Cells[i, 4].Value = sourceCell.GetFormattedValue();
}
// Auto-fit columns
for (int i = 0; i < 5; i++)
{
ws.Columns[i].AutoFit();
}
ef.Save("Number Format.xlsx");
}
public void TranslatesAddress(string address, int expectedColumn, int expectedRow)
{
var cell = new ExcelCell(address, "");
Assert.AreEqual(expectedColumn, cell.Column);
Assert.AreEqual(expectedRow, cell.Row);
}
static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Hyperlinks");
ws.Cells["A1"].Value = "Hyperlink examples:";
ExcelCell cell = ws.Cells["B3"];
cell.Value = "GemBoxSoftware";
cell.Style.Font.UnderlineStyle = UnderlineStyle.Single;
cell.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue);
cell.Hyperlink.Location = "https://www.gemboxsoftware.com";
cell.Hyperlink.IsExternal = true;
cell = ws.Cells["B5"];
cell.Value = "Jump";
cell.Style.Font.UnderlineStyle = UnderlineStyle.Single;
cell.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue);
cell.Hyperlink.ToolTip = "This is tool tip! This hyperlink jumps to E1.";
cell.Hyperlink.Location = ws.Name + "!E1";
ws.Cells["E1"].Value = "Destination";
cell = ws.Cells["B8"];
cell.Formula = "=HYPERLINK(\"https://www.gemboxsoftware.com/spreadsheet/examples/excel-cell-hyperlinks/207\", \"Example of HYPERLINK formula\")";
cell.Style.Font.UnderlineStyle = UnderlineStyle.Single;
cell.Style.Font.Color = SpreadsheetColor.FromName(ColorName.Blue);
ef.Save("Hyperlinks.xlsx");
}
/// <summary>
/// Adds the selected field to the bottom of the excel columns
/// </summary>
public void AddFieldToExcelColumns()
{
string field = this.SelectedField;
if (this.SelectedField == "-TEXT-")
{
TextPromptView textWindow = new TextPromptView
{
DataContext = new TextPromptViewModel("Add Field", "Field", "string")
};
textWindow.ShowDialog();
if (textWindow.DialogResult == true)
{
field = '"' + (textWindow.DataContext as TextPromptViewModel).Field1Value + '"';
}
else
{
return;
}
}
ExcelCell newCell = new ExcelCell(
this.ExcelId,
this.ExcelColumns.Count + 1,
field, "",
this.ExcelCustomer,
ExcelService.ReturnColumLetter(this.ExcelColumns.Count + 1));
this.ExcelColumns.Add(newCell);
}
public void TestWrite()
{
string file = Path.GetTempFileName() + ".xls";
using (ExcelDocument doc = ExcelDocument.CreateDocument())
{
ExcelWorksheet worksheet = doc.CreateWorksheet("Test");
ExcelCell cell = worksheet.GetCellAt(new ExcelCellName(new ColumnLetter(Letter.A), 1));
cell.Text = "Hello World";
ExcelFormatter formatter = worksheet.GetFormatterAt(
new ExcelCellName(new ColumnLetter(Letter.A), 1),
new ExcelCellName(new ColumnLetter(Letter.C), 3));
formatter.Background = Color.Red;
formatter.Foreground = Color.Yellow;
formatter.BorderColor = Color.Blue;
formatter.Font = new Font("Courier New", 12, FontStyle.Underline | FontStyle.Bold);
formatter.Merge();
formatter.ColumnWidth = 100;
formatter.RowHeight = 100;
Assert.AreEqual(Color.FromArgb(0, 255, 0, 0), formatter.Background);
Assert.AreEqual(Color.FromArgb(0, 255, 255, 0), formatter.Foreground);
//Assert.AreEqual(Color.FromArgb(0, 0, 0, 255), formatter.BorderColor); TODO
doc.Save(file);
}
}
private void setLoad(ExcelCell cell, int j, ref string day, ref string start_hour, ref string stop_hour, ref string curs_hours, ref string pregatire_hours, ref string recuperare_hours, ref string final_hour)
{
switch (j)
{
case 0:
day = cell.Value.ToString();
break;
case 1:
start_hour = cell.Value.ToString();
break;
case 2:
stop_hour = cell.Value.ToString();
break;
case 3:
curs_hours = cell.Value.ToString();
break;
case 4:
pregatire_hours = cell.Value.ToString();
break;
case 5:
recuperare_hours = cell.Value.ToString();
break;
case 6:
final_hour = cell.Value.ToString();
break;
}
}
protected virtual void AddEntry(
int entryIndex,
decimal billableHours,
decimal ratePerHour,
int weekNumber,
DateTime startDate,
DateTime endDate,
InvoiceConfiguration invoiceConfiguration,
bool isSplitRate = false)
{
Excel.Application excelApplication = ExcelGenerator.GetExcelApplication();
// Qty...
ExcelCell qtyStartCell = Helpers.Excel.ToCell(invoiceConfiguration.QtyStartCell);
excelApplication.Cells[qtyStartCell.Row + entryIndex, qtyStartCell.Column].Value = billableHours;
// Description...
ExcelCell descriptionStartCell = Helpers.Excel.ToCell(invoiceConfiguration.DescriptionStartCell);
string description = string.Format("{0} | {1}", invoiceConfiguration.Description, this.GetTimesheetWeekString(weekNumber, startDate, endDate, isSplitRate));
excelApplication.Cells[descriptionStartCell.Row + entryIndex, descriptionStartCell.Column].Value = description;
// PO Number...
int PONumber = invoiceConfiguration.PONumber;
ExcelCell POStartCell = Helpers.Excel.ToCell(invoiceConfiguration.PONumberStartCell);
excelApplication.Cells[POStartCell.Row + entryIndex, POStartCell.Column].Value = invoiceConfiguration.PONumber;
// Rate...
ExcelCell rateStartCell = Helpers.Excel.ToCell(invoiceConfiguration.RateStartCell);
excelApplication.Cells[rateStartCell.Row + entryIndex, rateStartCell.Column].Value = ratePerHour;
}
private static ICell CreateCell(IRow row, int cellIndex, ExcelCell excelCell)
{
switch (excelCell.Type)
{
case ExcelCellTypes.Numberic:
{
ICell cell = row.CreateCell(cellIndex, CellType.Numeric);
cell.SetCellValue((double)excelCell.Value);
return(cell);
}
case ExcelCellTypes.String:
{
ICell cell = row.CreateCell(cellIndex, CellType.String);
cell.SetCellValue(excelCell.Value.ToString());
return(cell);
}
case ExcelCellTypes.Null:
{
ICell cell = row.CreateCell(cellIndex, CellType.Blank);
return(cell);
}
default:
throw new NotSupportedException("不支持的数据格式");
}
}
public ExcelCellDiff(int columnIndex, int rowIndex, ExcelCell src, ExcelCell dst, ExcelCellStatus status)
{
ColumnIndex = columnIndex;
RowIndex = rowIndex;
SrcCell = src;
DstCell = dst;
Status = status;
}
/// <summary>
/// Import the data from the Spreadsheet
/// </summary>
/// <returns>Returns a list of JObjects</returns>
public virtual List <ExcelCell> Import(bool ignoreRowWithEmptyCells = false)
{
if (Workbook == null)
{
throw new MissingMemberException("Missing Workbook");
}
var sheet = Workbook.Worksheets.FirstOrDefault();
if (sheet == null)
{
throw new MissingMemberException("No sheet found");
}
for (int i = sheet.Dimension.Start.Row; i <= sheet.Dimension.End.Row; i++)
{
for (int j = sheet.Dimension.Start.Column; j <= sheet.Dimension.End.Column; j++)
{
ExcelCell cell = null;
if (ignoreRowWithEmptyCells)
{
if (sheet.Cells[i, j].Value != null && !string.IsNullOrEmpty(sheet.Cells[i, j].Value.ToString()))
{
continue;
}
else
{
cell = new ExcelCell {
CellName = sheet.Cells[i, j].Address, CellValue = sheet.Cells[i, j].Value
};
}
}
else
{
cell = new ExcelCell {
CellName = sheet.Cells[i, j].Address, CellValue = sheet.Cells[i, j].Value
};
}
try
{
Sheet.Add(cell);
}
catch (Exception ex)
{
ErrorList.AddError(new ValidationErrorItem
{
Location = sheet.Cells[i, j].Address,
Row = i,
Exception = ex
});
}
}
rowCount++;
}
return(Sheet);
}
bool DrawNameCell(ExcelCell cell)
{
_defaultBgColor = GUI.backgroundColor;
GUI.backgroundColor = cell.ruleColor;
bool dirty = DrawCell(cell);
GUI.backgroundColor = _defaultBgColor;
return(dirty);
}
public void AddFields(ExcelCell cell)
{
foreach (Match m in FieldRegex.Matches(cell.Value.ToString()))
{
var field = new Field(m.Groups[0].Value, cell);
field.UpdateParameters(Fields);
Fields.Add(field);
}
}
public ExcelCellDiff(int columnIndex, int rowIndex, ExcelCell src, ExcelCell dst, ExcelCellStatus status, ExcelCellMergeStatus mergeStatus = ExcelCellMergeStatus.None)
{
ColumnIndex = columnIndex;
RowIndex = rowIndex;
SrcCell = src;
DstCell = dst;
Status = status;
MergeStatus = mergeStatus;
}
/// <summary>
/// 执行数据导入的操作。
/// </summary>
/// <returns></returns>
public virtual void Commit()
{
try {
if (!_DataTableIsAutoCreate)
{
bool b = setColumnAndIndex(_DtData);
if (!b)
{
return;
}
}
ExcelWorksheet sheet = _ExcelObj.Worksheets.ActiveWorksheet;
//表示不存在任何记录。
if (sheet.Rows.Count == 1)
{
return;
}
int rowCount = sheet.Rows.Count;
int colCount = sheet.Rows[0].AllocatedCells.Count;
if (colCount == 0)
{
return;
}
for (int i = 1; i < rowCount; i++)
{
bool isEmptyRow = true;
ExcelRow dataRow = sheet.Rows[i];
DataRow newDr = _DtData.NewRow();
for (int j = 0; j < colCount; j++)
{
if (!_ColumnAndIndex.ContainsKey(j))
{
continue;
}
DataColumn dc = (DataColumn)_ColumnAndIndex[j];
ExcelCell cell = dataRow.AllocatedCells[j];
object val = cell.Value;
if (val != null && !string.IsNullOrEmpty(val.ToString().TrimEnd()))
{
isEmptyRow = false;
}
OnProviderProgress(dc.ColumnName, ref val);
setRowValue(newDr, val, dc.ColumnName, dc.DataType);
}
//如果是空行的话，不加到返回的DataTable中
if (!isEmptyRow)
{
_DtData.Rows.Add(newDr);
}
}
}
catch (Exception ex) {
throw ex;
}
return;
}
public Field(string template, ExcelCell cell)
{
Cell = cell;
Parameters = new List <FieldParameter>();
Template = template;
foreach (Match m in CellValueRegex.Matches(Template))
{
Parameters.Add(new FieldParameter(m.Groups[0].Value));
}
}
private ExcelRow CreateRowForItem(JobItemDocumentVM item)
{
var cells = new ExcelCell[item.Macros.Length + 3];
var errors = new List <string>();
if (item.Issues.Any())
{
errors.Add($"[File] - {string.Join(", ", item.Issues)}");
}
var cellColor = default(KnownColor?);
switch (item.Status)
{
case Common.Services.JobItemStatus_e.Failed:
cellColor = KnownColor.Red;
break;
case Common.Services.JobItemStatus_e.InProgress:
cellColor = KnownColor.LightBlue;
break;
case Common.Services.JobItemStatus_e.Succeeded:
cellColor = KnownColor.LightGreen;
break;
case Common.Services.JobItemStatus_e.Warning:
cellColor = KnownColor.LightYellow;
break;
case Common.Services.JobItemStatus_e.AwaitingProcessing:
cellColor = KnownColor.LightGray;
break;
}
cells[0] = new ExcelCell(item.Status, null, cellColor);
cells[1] = new ExcelCell(item.DisplayObject.Path, null, cellColor);
for (int i = 0; i < item.Macros.Length; i++)
{
var macro = item.Macros[i];
if (macro.Issues.Any())
{
errors.Add($"[{macro.Name}] - {string.Join("; ", macro.Issues)}");
}
cells[i + 2] = new ExcelCell(macro.Status, null, cellColor);
}
cells[cells.Length - 1] = new ExcelCell(string.Join(Environment.NewLine, errors), null, cellColor);
return(new ExcelRow(cells));
}
public void TemplateRepositoryTests_InsertExcelLayoutColumns_ShouldInsert()
{
#region Assemble
DbHelpers.ClearDatabase();
TemplateRepository templateRepository = new TemplateRepository(DbHelpers.GetContextFactory());
ConnectionManager connectionManager = new ConnectionManager(@"(local)\SQLExpress", "Odin");
connectionManager.SetUseTrustedConnection(true);
LogServiceFactory logServiceFactory = new LogServiceFactory("Odin");
OdinContextFactory OdinContextFactory = new OdinContextFactory(connectionManager, logServiceFactory);
ObservableCollection <ExcelCell> cellList = new ObservableCollection <ExcelCell>();
ExcelCell excelCell = new ExcelCell();
excelCell.ExcelId = 4;
excelCell.ColumnNumber = 7;
excelCell.Field = "TestField";
excelCell.Option = "TestOption";
excelCell.Customer = "TestCustomer";
cellList.Add(excelCell);
ExcelCell excelCell2 = new ExcelCell();
excelCell2.ExcelId = 4;
excelCell2.ColumnNumber = 8;
excelCell2.Field = "TestField2";
excelCell2.Option = "TestOption2";
excelCell2.Customer = "TestCustomer2";
cellList.Add(excelCell2);
#endregion // Assemble
#region Act
templateRepository.InsertExcelLayoutColumns(cellList);
#endregion // Act
#region Assert
using (OdinContext context = OdinContextFactory.CreateContext())
{
List <OdinExcelLayoutData> odinExcelLayoutData = (from o in context.OdinExcelLayoutData select o).ToList();
Assert.AreEqual("4", Convert.ToString(odinExcelLayoutData[0].LayoutId));
Assert.AreEqual("7", Convert.ToString(odinExcelLayoutData[0].ColumnNumber));
Assert.AreEqual("TestField", odinExcelLayoutData[0].Field);
Assert.AreEqual("TestOption", odinExcelLayoutData[0].Option);
Assert.AreEqual("TestCustomer", odinExcelLayoutData[0].Customer);
Assert.AreEqual("4", Convert.ToString(odinExcelLayoutData[1].LayoutId));
Assert.AreEqual("8", Convert.ToString(odinExcelLayoutData[1].ColumnNumber));
Assert.AreEqual("TestField2", odinExcelLayoutData[1].Field);
Assert.AreEqual("TestOption2", odinExcelLayoutData[1].Option);
Assert.AreEqual("TestCustomer2", odinExcelLayoutData[1].Customer);
}
#endregion // Assert
}
private ExcelRow ParseExcelRow(IRow r, int idx)
{
ExcelRow row = new ExcelRow(idx, this);
for (int i = 0; i < m_nPropertyNums; i++)
{
ExcelCell c = ParseExcelCell(r.GetCell(i), _Properties[i], row);
row.AppendCell(c);
}
return(row);
}
bool DrawCell(ExcelCell cell)
{
string s = EditorGUILayout.TextField(cell.stringValue);
if (s != cell.stringValue)
{
cell.stringValue = s;
return(true);
}
return(false);
}
public void As_converts_cell_value_type_to_generic_argument_type()
{
var newCell = new ExcelCell("2");
Assert.AreEqual(2, newCell.Cast<int>());
Assert.AreEqual(typeof(int), newCell.Cast<int>().GetType());
}
public void Cell_implicitly_converts_to_string()
{
var newCell = new ExcelCell("some value");
Assert.IsTrue("some value" == newCell);
}
public void Constructor_sets_cell_value()
{
var newCell = new ExcelCell("hello");
Assert.AreEqual("hello", newCell.Value);
}
private void ImportStyle(ExcelCell excelCell)
{
// Not implemented
// ShrinkToFit
// WrapText
CellStyle style = excelCell.Style;
// Import font
// Not implemented
// ScriptPosition
// UnderlineStyle
// Strikeout
ExcelFont font = style.Font;
Label.FontFamily = new FontFamily(font.Name);
Label.Foreground = new SolidColorBrush(font.Color.ConvertToNewColor());
Label.FontStyle = font.Italic ? FontStyles.Italic : FontStyles.Normal;
Label.FontWeight = FontWeight.FromOpenTypeWeight(font.Weight);
Label.FontSize = font.Size / 20d;
// Fill pattern
ApplyFillPattern(style);
// Alignment
ApplyAlignment(style);
// Indent
Label.Padding = new Thickness(style.Indent, 0, style.Indent, 0);
// Text Vertical
Label.RenderTransform = new RotateTransform((style.IsTextVertical ? 90 : 0) + style.Rotation);
// Locked
if (style.Locked)
IsReadOnly = true;
// Number format
NumberFormat = style.NumberFormat;
// Borders
// TODO Implement borders
//ImportBorders(style.Borders);
}
private void DoRow(ref int rownum, ExcelRow row, ExcelSheet sheet, ExcelWorksheet exSheet, object[] fileargs = null, object[] sheetargs = null)
{
//Process Cell
if (row.SQL != ExcelSQLType.PlainText)
{
using (DbCommand cmd = conn.RowConnection.CreateCommand())
{
cmd.CommandTimeout = conn.CommandTimeout;
cmd.CommandType = row.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text;
cmd.CommandText = row.Text;
if (fileargs != null)
for (int i = 0; i < fileargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@F" + i : "F" + i;
param.Value = fileargs[i];
cmd.Parameters.Add(param);
}
if (sheetargs != null)
for (int i = 0; i < sheetargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@S" + i : "S" + i;
param.Value = sheetargs[i];
cmd.Parameters.Add(param);
}
using (DbDataReader reader = cmd.ExecuteReader())
{
if (row.ColumnHeader)
{
int curcell = 1;
using (var rw = exSheet.Cells[++rownum + ":" + rownum])
{
Console.WriteLine("Writing row {0} with Column header...", rw.Address);
var cols = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToArray();
for (int i = 0; i < reader.FieldCount; i++)
{
ExcelCell dc = new ExcelCell();
dc.SQL = ExcelSQLType.PlainText;
dc.Text = cols[i];
//Console.WriteLine("Column '{0}' is have a type of '{1}'", cols[i], (reader.GetFieldType(i) ?? typeof(string)).FullName);
DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, cols);
}
exSheet.View.FreezePanes(2, 1);
}
}
while (reader.Read())
{
int curcell = 1;
using (var rw = exSheet.Cells[++rownum + ":" + rownum])
{
//Console.WriteLine("Writing row {0} with given SQL...", rw.Address);
object[] args = new object[reader.FieldCount];
reader.GetValues(args);
for (int i = 0; i < reader.FieldCount; i++)
{
if (row.Fetch != null)
{
using (DbCommand subcmd = conn.Connection.CreateCommand())
{
cmd.CommandTimeout = conn.CommandTimeout;
subcmd.CommandType = row.Fetch.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text;
subcmd.CommandText = row.Fetch.Text;
if (fileargs != null)
for (int j = 0; j < fileargs.Length; j++)
{
DbParameter param = subcmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = subcmd.Connection is System.Data.SqlClient.SqlConnection ? "@F" + j : "F" + j;
param.Value = fileargs[j];
subcmd.Parameters.Add(param);
}
if (sheetargs != null)
for (int j = 0; j < sheetargs.Length; j++)
{
DbParameter param = subcmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = subcmd.Connection is System.Data.SqlClient.SqlConnection ? "@S" + j : "S" + j;
param.Value = sheetargs[j];
subcmd.Parameters.Add(param);
}
for (int j = 0; j < args.Length; j++)
{
DbParameter param = subcmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = subcmd.Connection is System.Data.SqlClient.SqlConnection ? "@R" + j : "R" + j;
param.Value = args[j];
subcmd.Parameters.Add(param);
}
using (DbDataReader subreader = subcmd.ExecuteReader())
{
if (row.Fetch.Type == ExcelFetchType.Single)
{
if (!subreader.Read()) continue;
for (int j = 0; j < subreader.FieldCount; j++)
{
ExcelCell dc = new ExcelCell();
dc.SQL = ExcelSQLType.PlainText;
dc.Text = subreader.GetValue(j).ToString();
dc.Out = DetermineOutType(subreader.GetFieldType(j));
DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, args);
}
}
else
{
while (subreader.Read())
{
ExcelCell dc = new ExcelCell();
dc.SQL = ExcelSQLType.PlainText;
dc.Text = subreader.GetValue(0).ToString();
dc.Out = DetermineOutType(subreader.GetFieldType(0));
DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, args);
}
}
}
}
}
else
{
ExcelCell dc = new ExcelCell();
dc.SQL = ExcelSQLType.PlainText;
dc.Text = reader.GetValue(i).ToString();
dc.Out = DetermineOutType(reader.GetFieldType(i));
DoCell(ref curcell, dc, row, sheet, rw, fileargs, sheetargs, args);
}
}
}
}
}
}
}
else
{
int curcell = 1;
using (var rw = exSheet.Cells[++rownum + ":" + rownum])
{
//Console.WriteLine("Writing row {0}...", rw.Start.Row);
for (int i = 0; i < (row.Cells != null ? row.Cells.Count : 0); i++) DoCell(ref curcell, row.Cells[i], row, sheet, rw, fileargs, sheetargs);
}
}
}
public void to_string_returns_value_as_string()
{
var newCell = new ExcelCell("hello");
Assert.AreEqual("hello", newCell.ToString());
}
private void DoCell(ref int cellnum, ExcelCell cell, ExcelRow row, ExcelSheet sheet, ExcelRange exRow, object[] fileargs = null, object[] sheetargs = null, object[] rowargs = null)
{
//Cell Value
int cnt = cellnum;
Action<string> cellfunc = val =>
{
using (var cl = exRow[exRow.Start.Row, cnt++])
{
//Console.WriteLine("Writing cell {0}...", cl.Start.Address);
if (val != null)
{
double nval;
long lval;
DateTime dval;
bool isFomula = !string.IsNullOrEmpty(val) && val[0] == '=';
string rawval = isFomula ? val.Substring(1) : val;
switch (cell.Out)
{
case ExcelOutType.DateTime:
cl.Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
if (isFomula) cl.Formula = rawval; else if (DateTime.TryParse(rawval, out dval)) cl.Value = dval; else cl.Value = rawval;
if (cl.Text.EndsWith("00:00:00"))
cl.Style.Numberformat.Format = "yyyy-mm-dd";
break;
case ExcelOutType.Date:
cl.Style.Numberformat.Format = "yyyy-mm-dd";
if (isFomula) cl.Formula = rawval; else if (DateTime.TryParse(rawval, out dval)) cl.Value = dval; else cl.Value = rawval;
break;
case ExcelOutType.Integer:
cl.Style.Numberformat.Format = "#,##0";
if (isFomula) cl.Formula = rawval; else if (long.TryParse(rawval, out lval)) cl.Value = lval; else cl.Value = rawval;
break;
case ExcelOutType.Number:
cl.Style.Numberformat.Format = "#,##0.00";
if (isFomula) cl.Formula = rawval; else if (double.TryParse(rawval, out nval)) cl.Value = nval; else cl.Value = rawval;
break;
case ExcelOutType.Money:
cl.Style.Numberformat.Format = "￦ #,##0";
if (isFomula) cl.Formula = rawval; else if (long.TryParse(rawval, out lval)) cl.Value = lval; else cl.Value = rawval;
break;
case ExcelOutType.Normal:
default:
if (isFomula) cl.Formula = rawval; else cl.Value = rawval;
break;
}
}
}
};
if (cell.SQL != ExcelSQLType.PlainText)
{
using (DbCommand cmd = conn.CellConnection.CreateCommand())
{
cmd.CommandTimeout = conn.CommandTimeout;
cmd.CommandType = cell.SQL == ExcelSQLType.Procedure ? CommandType.StoredProcedure : CommandType.Text;
cmd.CommandText = cell.Text;
if (fileargs != null)
for (int i = 0; i < fileargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@F" + i : "F" + i;
param.Value = fileargs[i];
cmd.Parameters.Add(param);
}
if (sheetargs != null)
for (int i = 0; i < sheetargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@S" + i : "S" + i;
param.Value = sheetargs[i];
cmd.Parameters.Add(param);
}
if (rowargs != null)
for (int i = 0; i < rowargs.Length; i++)
{
DbParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = cmd.Connection is System.Data.SqlClient.SqlConnection ? "@R" + i : "R" + i;
param.Value = rowargs[i];
cmd.Parameters.Add(param);
}
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cellfunc.Invoke(reader.GetValue(0).ToString());
}
}
}
}
else if(cell.Text != null)
{
cellfunc.Invoke(cell.Text);
}
cellnum = cnt;
}
/// <summary>
/// 向当前工作区的表中插入一个单元格
/// </summary>
/// <param name="worksheetPart">当前的工作区对象</param>
/// <param name="cell">设置要插入的单元格信息</param>
/// <returns>返回插入成功的数量</returns>
public static bool InsertCell(this WorksheetPart worksheetPart, ExcelCell cell)
{
return InsertCell(worksheetPart, new List<ExcelCell> { cell }) > 0;
}
public void ValueAs_returns_default_generic_value_when_value_is_DBNull()
{
var newCell = new ExcelCell(DBNull.Value);
Assert.AreEqual(0, newCell.Cast<int>());
}
public DataTable GetSpreadsheet(string sheetName, ExcelCell startingCell, ExcelCell endingCell)
{
if (string.IsNullOrEmpty(sheetName)) throw new ArgumentNullException("sheetName", "sheetName cannot be null or empty");
DataTable dtResult = new DataTable();
using (var connection = new OleDbConnection(GetConnectionString())) {
connection.Open();
if (!sheetName.EndsWith("$")) sheetName += "$";
using (var command = new OleDbCommand("Select * From [" + sheetName + startingCell.Cell + ":" + endingCell.Cell + "]", connection)) {
using (var adapter = new OleDbDataAdapter(command)) {
using (DataSet ds = new DataSet()) {
adapter.Fill(ds);
if (ds.Tables.Count > 0) dtResult = ds.Tables[0];
ds.Dispose();
}
}
}
connection.Close();
}
return dtResult;
}
public void ImportData(ExcelCell excelCell)
{
// Check if style to import is default
if (!excelCell.IsStyleDefault) {
this.isDefault = false;
}
else {
// Cell Value
if (excelCell.Value == null) {
SetValue(String.Empty);
}
else {
SetValue(excelCell.Value);
}
Formula = FormulaManager.GetExpression(excelCell.Formula);
IsFormulaHidden = excelCell.Style.FormulaHidden;
// Style
ImportStyle(excelCell);
}
}
