private ExcelRow GetRow(Row row, List <string> headrCellNames, Dictionary <string, string> headerColumnNames)
{
uint rowIndex = row.RowIndex;
IEnumerable <Cell> cells = row.Descendants <Cell>();
Dictionary <string, Cell> cellDict = new Dictionary <string, Cell>();
foreach (Cell cell in cells)
{
cellDict[cell.CellReference] = cell;
}
ExcelRow excelRow = new ExcelRow();
foreach (string headerCellName in headrCellNames)
{
string columnName = headerColumnNames[headerCellName];
string cellName = columnName + rowIndex;
string value = null;
if (cellDict.ContainsKey(cellName))
{
Cell cell = cellDict[cellName];
value = GetCellValue(cell);
}
excelRow.Add(cellName, value);
}
return(excelRow);
}
public ExcelRow InsertRow(int rowPosition, string namedStyle)
{
ExcelRow row = this.InsertRow(rowPosition);
row.StyleName = namedStyle;
return(row);
}
//创建一个空的表结构
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);
}
private static void SingleFillEmptyStation(ExcelWorksheet worksheet, string type, ref int rowindex, int copyFrom, int station, bool createRight)
{
int colIndex = 1;
worksheet.Cells[rowindex, 1].Value = string.Format("{0}{1:d3}", type, station * 10);
rowindex++;
ExcelRow row = worksheet.Row(copyFrom);
for (int i = 1; i < 17; i++)
{
worksheet.Cells[rowindex, i].Value = worksheet.Cells[copyFrom, i].Value;
}
rowindex++;
//该工位下步骤数据
//步骤
for (int j = 0; j < 30; j++)
{
colIndex = 1;
worksheet.Cells[rowindex, colIndex].Value = "装配物料" + (j + 1);//left
if (createRight)
{
colIndex = 9;
worksheet.Cells[rowindex, colIndex].Value = "装配物料" + (j + 1);//left
}
rowindex++;
}
}
public static string ToHtml(this ExcelWorksheet sheet)
{
int lastRow = sheet.Dimension.Rows;
int lastCol = sheet.Dimension.Columns;
HtmlElement htmlTable = new HtmlElement("table");
htmlTable.Attributes["cellspacing"] = 0;
htmlTable.Styles["white-space"] = "nowrap";
//render rows
for (int row = 1; row <= lastRow; row++)
{
ExcelRow excelRow = sheet.Row(row);
var test = excelRow.Style;
HtmlElement htmlRow = htmlTable.AddChild("tr");
htmlRow.Styles.Update(excelRow.ToCss());
for (int col = 1; col <= lastCol; col++)
{
ExcelRange excelCell = sheet.Cells[row, col];
HtmlElement htmlCell = htmlRow.AddChild("td");
htmlCell.Content = excelCell.Text;
htmlCell.Styles.Update(excelCell.ToCss());
}
}
return(htmlTable.ToString());
}
public List <ExcelRow> ParseRows(List <ExcelColumn> columns, ISheet worksheet, CellRangeAddress startCell)
{
var rows = new List <ExcelRow>();
int activeRowNumber = startCell.FirstRow + 1;
var currentRow = worksheet.GetRow(activeRowNumber);
while (currentRow != null)
{
var row = new ExcelRow();
foreach (var cell in currentRow)
{
row.Add(cell.StringCellValue);
}
rows.Add(row);
activeRowNumber = activeRowNumber + 1;
currentRow = worksheet.GetRow(activeRowNumber);
}
return(rows);
}
private WorkLogDataRow ImportRow(ExcelRow excelRow, SourceFileSettings settings)
{
WorkLogDataRow row = new WorkLogDataRow();
CellRange cells = excelRow.Cells;
row.Key = GetStringValue(cells, settings.KeyField.Position - 1);
row.Title = GetStringValue(cells, settings.TitleField.Position - 1);
row.Assignee = GetStringValue(cells, settings.AssigneeField.Position - 1);
row.Status = GetStringValue(cells, settings.StatusField.Position - 1);
row.WorkDateStr = GetStringValue(cells, settings.DateField.Position - 1);
row.UserName = GetStringValue(cells, settings.UserNameField.Position - 1);
row.Url = GetStringValue(cells, settings.UrlField.Position - 1);
if (settings.EstimationField.IsUseStoryPointsFromMainPosition)
{
row.OriginalEstimateHours = GetStringValue(cells, settings.EstimationField.AlternatePosition - 1);
row.OriginalEstimateStoryPoints = GetStringValue(cells, settings.EstimationField.Position - 1);
}
else
{
row.OriginalEstimateHours = GetStringValue(cells, settings.EstimationField.Position - 1);
row.OriginalEstimateStoryPoints = "0";
}
row.OriginalEstimateHours = RemoveSymbol(row.OriginalEstimateHours, 'h');
row.OriginalEstimateStoryPoints = RemoveSymbol(row.OriginalEstimateStoryPoints, 'h');
row.TimeSpent = RemoveSymbol(GetStringValue(cells, settings.TimeSpentField.Position - 1), 'h');
return(row);
}
public static void AddElement(RjElement parent, ExcelWorksheet sheet, ExcelRow row, int index, string key)
{
string typeString = GetSheetCell(sheet, KEY_TYPE_ROW_INDEX, index).ToString();
RjValueType type = RjValueType.INT;
if (typeString == "int")
{
type = RjValueType.INT;
}
else if (typeString == "float")
{
type = RjValueType.FLOAT;
}
else if (typeString == "bool")
{
type = RjValueType.BOOL;
}
else
{
type = RjValueType.STRING;
}
// parent.AddElement(new RjValue(key, row[index] ,_value_type, GetSheetCell(sheet, DES_TYPE_ROW_INDEX, index).ToString()));
var valueString = GetSheetCell(sheet, row.Row, index).ToString();
var rjValue = new RjValue(key, valueString, type, GetSheetCell(sheet, DES_TYPE_ROW_INDEX, index).ToString());
parent.AddElement(rjValue);
}
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);
}
}
private ExcelRow ReadExcelRow(int rowIndex, OpenXmlElement row, IReadOnlyList <ExcelColumn> excelColumns, SpreadsheetDocument doc)
{
var excelRow = new ExcelRow
{
Index = rowIndex
};
foreach (var cell in row.Elements <Cell>())
{
var columnIndex = GetColumnIndexFromCellReference(cell.CellReference.Value);
if (columnIndex == -1)
{
continue;
}
var column = excelColumns[columnIndex];
var value = GetCellValue(doc, cell);
excelRow.Cells.Add(new ExcelCell
{
Column = column,
Value = value
});
}
// fill in empty cells
FillEmptyRowCells(excelRow, excelColumns);
return(excelRow);
}
private void addNewRowToTheEnd(string worksheetName, TableDescriptionTableEntry descriptionEntry, Dictionary <string, string> record, int rowNumber)
{
if (descriptionEntry.Column_Number == 0)
{
// create a new row at the specified row index with the first cell as the value cell
m_reportTable.WorkSheets[worksheetName].Add(
new ExcelRow(new List <ExcelCell>()
{
new ExcelCell(
record.Exists(descriptionEntry.Access_Column_Name) ? record[descriptionEntry.Access_Column_Name].NotEmpty() ? record[descriptionEntry.Access_Column_Name] : "" : "")
{
CellFormat = descriptionEntry.Excel_Cell_Format.NotEmpty() ? descriptionEntry.Excel_Cell_Format : "",
Color = descriptionEntry.Background_Color.NotEmpty() ? descriptionEntry.Background_Color : "",
TextColor = descriptionEntry.Foreground_Color.NotEmpty() ? descriptionEntry.Foreground_Color : "",
FontSize = descriptionEntry.Font_Size
}
}));
}
else
{
// create a new row with some blank cells in front of the actual value cell
int blankColumns = descriptionEntry.Column_Number;
ExcelRow newRow = new ExcelRow();
addBlankCells(ref newRow, blankColumns); // add some blank cells to the new row
newRow.Add(new ExcelCell(
record.Exists(descriptionEntry.Access_Column_Name) ? record[descriptionEntry.Access_Column_Name].NotEmpty() ? record[descriptionEntry.Access_Column_Name] : "" : "")
{
CellFormat = descriptionEntry.Excel_Cell_Format.NotEmpty() ? descriptionEntry.Excel_Cell_Format : "",
Color = descriptionEntry.Background_Color.NotEmpty() ? descriptionEntry.Background_Color : "",
TextColor = descriptionEntry.Foreground_Color.NotEmpty() ? descriptionEntry.Foreground_Color : "",
FontSize = descriptionEntry.Font_Size
});
m_reportTable.WorkSheets[worksheetName].Add(newRow); // add the new row to the table
}
}
public void index_maps_cells_correctly()
{
var row = new ExcelRow(_cells, _columnMappings);
Assert.AreEqual(_cells[0], row[0]);
Assert.AreEqual(_cells[1], row[1]);
Assert.AreEqual(_cells[2], row[2]);
}
public void column_name_index_maps_cells_correctly()
{
var row = new ExcelRow(_cells, _columnMappings);
Assert.AreEqual(_cells[0], row["Name"]);
Assert.AreEqual(_cells[1], row["Favorite Sport"]);
Assert.AreEqual(_cells[2], row["Age"]);
}
public static TransactionItem Map(ExcelRow request, WorkbookPart workbookPart)
{
var response = new TransactionItem();
foreach (var cell in request.Cells)
{
var column = cell.CellId.First().ToString();
switch (column.ToUpper())
{
case "A":
response.TransactionDate = DateTime.FromOADate(double.Parse(cell.Value));
break;
case "B":
var value = Math.Abs(decimal.Parse(cell.Value));
response.Amount = Math.Round(value, 2);
break;
case "C":
response.Description = ExcelHelper.GetSharedStringItemById(workbookPart, int.Parse(cell.Value));
break;
}
}
return(response);
}
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 Task <bool> ReadRowSpecificSheet()
{
if (!inSpecificSheet)
{
if (specificSheetIndex != 0)
{
var currentSheetReaderIndex = 0;
do
{
reader.NextResult();
currentSheetReaderIndex++;
} while (specificSheetIndex == currentSheetReaderIndex);
}
inSpecificSheet = true;
}
var found = reader.Read();
if (!found)
{
return(Task.FromResult(false));
}
index++;
Current = new ExcelRow(index, CurrentSheet, reader);
return(Task.FromResult(true));
}
//根据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 void execute(DBWrapper context)
{
// start at first data row and keep going until
// a non-data row is encountered.
// For each row:
// Generate a record object from its contents
// If there's already a matching record,
// update that record's contents from
// the fields of the object we just
// generated.
// Otherwise, add the object we just generated
// to short-term storage.
//
int row_idx = this.first_data_row_idx;
ExcelRow row = this.sheet[row_idx];
// This data section may end with a row containing
// "Counts households ..." in first column.
while ((row != null) && (this.IsDataRow(row)))
{
object new_rec = this.ObjectFromVestaRow(context, row, year);
this.add_or_update(new_rec, context);
row_idx++;
row = this.sheet[row_idx];
}
}
protected override object ObjectFromVestaRow(DBWrapper context, ExcelRow row, int year)
{
// Make an object to return
ServicesHouseholdEnrollment retObj = new ServicesHouseholdEnrollment();
// Get a date/time string and parse it:
DateTime dt;
string s = row[this.field_indices["Date Reserved/Enrolled"]];
// s represents a date and time. Parse it into a DateTime object:
if (!System.DateTime.TryParse(s, out dt)) // string parsed to double OK
{
// If parse fails, just use current date
dt = DateTime.Now;
}
retObj.year = dt.Year;
retObj.day = dt.Day;
retObj.month = dt.Month;
// The rest of the fields are simply strings:
retObj.service_type = row[this.field_indices["Partner Network Service"]];
retObj.head_of_household = row[this.field_indices["Head of Household"]];
retObj.family_id = row[this.field_indices["client ID"]];
retObj.phone = row[this.field_indices["Primary Phone"]];
retObj.address = row[this.field_indices["Address"]];
retObj.city = row[this.field_indices["City"]];
retObj.state_or_province = row[this.field_indices["State"]];
retObj.postal_code = row[this.field_indices["Zip"]];
return(retObj);
}
/// <summary>
/// The end of a participant report data section is marked
/// by a row with "Counts households ..." in the first column.
/// </summary>
/// <param name="row"></param>
/// <returns></returns>
protected override bool IsDataRow(ExcelRow row)
{
return((!row.IsEmpty()) &&
(!row[0].StartsWith("Counts households", ignoreCase: true,
culture: null))
);
}
private void addBlankCells(ref ExcelRow row, int count)
{
for (int i = 0; i < count; ++i)
{
row.Add(new ExcelCell());
}
}
public static List <ExcelRow> GetExcelRows(byte[] b)
{
List <ExcelRow> excelRowsList = new List <ExcelRow>();
MemoryStream stream = new MemoryStream(b);
using (ExcelPackage excelPackage = new ExcelPackage(stream))
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets["Sheet"];
for (int i = 3; i < worksheet.Dimension.Rows; i++)
{
ExcelRow row = new ExcelRow()
{
Id = Int32.Parse(worksheet.Cells[i, 1].Value.ToString()),
Name = worksheet.Cells[i, 2].Value.ToString(),
Description = worksheet.Cells[i, 3].Value.ToString(),
Source = worksheet.Cells[i, 4].Value.ToString(),
Aim = worksheet.Cells[i, 5].Value.ToString(),
IsCofidentialityViolation = worksheet.Cells[i, 6].Value.ToString() == "1" ? true : false,
IsIntegrityViolation = worksheet.Cells[i, 7].Value.ToString() == "1" ? true : false,
IsAvailabilityViolation = worksheet.Cells[i, 8].Value.ToString() == "1" ? true : false,
CreateDateTime = DateTime.FromOADate(Double.Parse(worksheet.Cells[i, 9].Value.ToString())),
UpdateDateTime = DateTime.FromOADate(Double.Parse(worksheet.Cells[i, 10].Value.ToString()))
};
excelRowsList.Add(row);
}
}
return(excelRowsList);
}
public bool MoveNext()
{
if (_estimateWorksheet != null)
{
try
{
if (_rowId == 1)
{
ExcelRow headerRow = _estimateWorksheet.Row(_rowId);
if (headerRow == null)
{
Logger.Error("Estimates Worksheet has no rows");
return(false);
}
Logger.Information("Reading Estimates ...");
}
Current = new Estimate();
++_rowId;
ExcelRow dataRow = _estimateWorksheet.Row(_rowId);
if (dataRow == null)
{
Logger.Warning("Finished reading Estimates worksheet");
return(false);
}
bool sayTrue = false;
if (_estimateWorksheet.Cells[_rowId, 1].Value != null)
{
Current.State = Convert.ToInt32(_estimateWorksheet.Cells[_rowId, 1].Value);
sayTrue = true;
}
if (_estimateWorksheet.Cells[_rowId, 2].Value != null)
{
Current.Districts = Convert.ToInt32(_estimateWorksheet.Cells[_rowId, 2].Value);
}
if (_estimateWorksheet.Cells[_rowId, 3].Value != null)
{
Current.EstimatesPopulation = Convert.ToInt32(_estimateWorksheet.Cells[_rowId, 3].Value);
}
if (_estimateWorksheet.Cells[_rowId, 4].Value != null)
{
Current.EstimateHoseholds = Convert.ToInt32(_estimateWorksheet.Cells[_rowId, 4].Value);
}
return(sayTrue);
}
catch (ArgumentOutOfRangeException)
{
Logger.Warning("RowId out of range");
}
}
Logger.Warning("Finished reading Estimates");
return(false);
}
public void column_name_index_maps_cells_correctly()
{
var row = new ExcelRow(_cells, _columnMappings);
Assert.AreEqual(_cells[0], row["Name"]);
Assert.AreEqual(_cells[1], row["Favorite Sport"]);
Assert.AreEqual(_cells[2], row["Age"]);
}
public static void InitSell(this ExcelRow row, int index, bool isBold, string value)
{
var easyGetCell = row.easy_getCell(index);
easyGetCell.setBold(isBold);
easyGetCell.setDataType("string");
easyGetCell.setValue(value);
}
public void AddRow(string fileName)
{
currentRow = new ExcelRow()
{
FileName = fileName
};
rows.Add(currentRow);
}
protected ExcelRow PopulateLine(IReportable item, int row, ExcelNamedStyleXml namedStyle)
{
ExcelRow resultRow = InsertRow(row, namedStyle);
PopulateLineGeneric(item, row);
return(resultRow);
}
public void invalid_column_name_index_throws_argument_exception()
{
Assert.Throws <ArgumentException>(() =>
{
var newRow = new ExcelRow(_cells, _columnMappings);
var temp = newRow["First Name"];
}, "'First Name' column name does not exist. Valid column names are 'Name', 'Favorite Sport', 'Age'");
}
protected ExcelRow PopulateLine(IReportable item, int row, ExcelRow copyStylesFromRow)
{
ExcelRow resultRow = InsertRow(row, copyStylesFromRow);
PopulateLineGeneric(item, row);
return(resultRow);
}
protected ExcelRow PopulateLine(IReportable item, int row)
{
ExcelRow resultRow = InsertRow(row);
this.PopulateLineGeneric(item, row);
return(resultRow);
}
public void index_maps_cells_correctly()
{
var row = new ExcelRow(_cells, _columnMappings);
Assert.AreEqual(_cells[0], row[0]);
Assert.AreEqual(_cells[1], row[1]);
Assert.AreEqual(_cells[2], row[2]);
}
protected ExcelRow CreateTableHeader(IReportable item, int row, int copyStylesFromRow)
{
ExcelRow resultRow = InsertRow(row, copyStylesFromRow);
CreateHeaderGeneric(item, row);
return(resultRow);
}
public ExcelWorkbook GenerateExcel(Dictionary<string, List<ExcelReportRow>> data, Dictionary<Guid, string> selectedShops)
{
ExcelWorkbook workbook = new ExcelWorkbook("Südameapteegi_ravimiinfo", "Südameapteegi ravimiinfo süsteem, fail on genereeritud " + DateTime.Now, "Südameapteek", false) { Styles = new List<ExcelStyle>(1) };
workbook.Styles.Add(new ExcelStyle(false, "black", false) { ID = "reg" });
workbook.Styles.Add(new ExcelStyle(true, "black", false) { ID = "bold" });
workbook.Styles.Add(new ExcelStyle(true, "red", false) { ID = "warn" });
workbook.Worksheet.Table = new Helmes.ExcelLib.ExcelTable();
ExcelRow headerRow=new ExcelRow();
//if (data != null && data.Count > ushort.MaxValue)
//{
// ExcelRow warningRow = new ExcelRow();
// warningRow.Cells.Add(new ExcelCell("warn", "NB! Failis on kokku " + data.Count + " rida, mida on rohkem kui " + ushort.MaxValue + ", mida saab Excelis maksimaalselt näidata. Seetõttu on fail poolik ning tuleb kitsendada väljavõtte filtrit!!"));
// workbook.Worksheet.Table.Rows.Add(warningRow);
//}
headerRow.Cells.Add(new ExcelCell("bold", "Apt kood"));
headerRow.Cells.Add(new ExcelCell("bold", "Ravimi nimetus"));
headerRow.Cells.Add(new ExcelCell("bold", "ATC"));
foreach (KeyValuePair<Guid,string> selectedShop in selectedShops)
{
headerRow.Cells.Add(new ExcelCell("bold", selectedShop.Value));
}
workbook.Worksheet.Table.Rows.Add(headerRow);
foreach (KeyValuePair<string,List<ExcelReportRow>> reportRow in data)
{
ExcelRow dataRow = new ExcelRow();
dataRow.Cells.Add(new ExcelCell("reg", reportRow.Key));
if(reportRow.Value!=null)
{
dataRow.Cells.Add(new ExcelCell("reg", reportRow.Value.FirstOrDefault().MedName));
dataRow.Cells.Add(new ExcelCell("reg", reportRow.Value.FirstOrDefault().AtcCode));
foreach (KeyValuePair<Guid, string> selectedShop in selectedShops)
{
ExcelReportRow rpr = reportRow.Value.FirstOrDefault(w => w.ShopId == selectedShop.Key);
if (rpr != null)
{
dataRow.Cells.Add(new ExcelCell("reg", rpr.Price));
}else
{
dataRow.Cells.Add(new ExcelCell());
}
}
}
workbook.Worksheet.Table.Rows.Add(dataRow);
}
return workbook;
}
private void GenerateSalesReport(DateTime date, ExcelRow row, int dbSupermarketId, int dbProductId)
{
var quantity = int.Parse(row.AllocatedCells[2].Value.ToString());
var unitPrice = decimal.Parse(row.AllocatedCells[3].Value.ToString());
var totalSum = decimal.Parse(row.AllocatedCells[4].Value.ToString());
var salesReport = new Sale
{
SupermarketId = dbSupermarketId,
ProductId = dbProductId,
Quantity = quantity,
UnitPrice = unitPrice,
TotalSum = totalSum,
Date = date
};
this.SalesReports.Add(salesReport);
}
private void GenerateSalesReport(DateTime date, ExcelRow row, int dbSupermarketId, int dbProductId)
{
var quantity = int.Parse(row.AllocatedCells[2].Value.ToString());
var salesReport = new Sale
{
SupermarketId = dbSupermarketId,
ProductId = dbProductId,
Quantity = quantity,
SoldDate = date
};
using (TransactionScope tran = new TransactionScope())
{
this.SqlMarketContext.Sales.Add(salesReport);
this.SqlMarketContext.SaveChanges();
tran.Complete();
}
}
public void processData()
{
ExcelSheet sheet = new ExcelSheet();
using(StreamReader file = new StreamReader(mFilename))
{
string line = null;
string[] row;
// Get to the data
while (line == null || line.Split(',')[1] != "Track")
{
line = file.ReadLine();
}
// Parse the data
while((line = file.ReadLine()) != null)
{
row = line.Split(',');
// If this cell does not have data then there is no more data.
if (row[4] == "")
{
break;
}
/*
* Column Name | Column Number
* Track | 1
* Direction | 2
* Move | 3
* Circuit | 4
* Brake Location | 5
* Target Location | 6
* Worst Grade | 8
* Entry Speed | 9
* Overspeed | 10
* Acceleration | 12
* Reaction Time | 14
* Brake Rate | 16
* Runaway Accel | 19
* Propulsion Remov | 21
* Brake Build up | 23
* Overhang Dist | 25
*/
ExcelRow curRow = new ExcelRow(
row[1] != "" ? Convert.ToInt32(row[1]) : (sheet.getRow(sheet.getCurrentRow() - 1)).getTrack(), // Track
row[2] != "" ? row[2] : (sheet.getRow(sheet.getCurrentRow() - 1)).getDirection(), // Direction
row[3] != "" ? row[3] : (sheet.getRow(sheet.getCurrentRow() - 1)).getMove(), // Move
row[4], // Circuit
Convert.ToInt32(row[5]), // Brake Location
Convert.ToInt32(row[6]), // Target Location
Convert.ToDouble(row[8]), // Worst Grade
Convert.ToDouble(row[9]), // Entry Speed
Convert.ToDouble(row[10]), // Overspeed
Convert.ToDouble(row[12]), // Acceleration
Convert.ToDouble(row[14]), // Reaction Time
Convert.ToDouble(row[16]), // Brake Rate
Convert.ToDouble(row[19]), // Runaway Accel
Convert.ToDouble(row[21]), // Propulsion Removal
Convert.ToInt32(row[23]), // Brake Build Up
Convert.ToInt32(row[25])); // Overhand Distance
sheet.addRow(curRow);
}
}
}
public Boolean addRow(ExcelRow row)
{
list.Add(row);
mCurrentRow++;
return true;
}
public void invalid_column_name_index_throws_argument_exception()
{
var newRow = new ExcelRow(_cells, _columnMappings);
var temp = newRow["First Name"];
}
public List<ExcelRow> ReadRows()
{
List<ExcelRow> tableData = new List<ExcelRow>();
foreach (var dataWithMeta in SheetDataWithMeta)
foreach(Row row in dataWithMeta.Item1.ChildElements)
{
//List<string> rowData = new List<string>();
ExcelRow rowData = new ExcelRow();
foreach (Cell c in row.ChildElements)
{
if (c == null || c.CellValue == null || string.IsNullOrWhiteSpace(c.CellValue.Text))
continue;
var styles = dataWithMeta.Item3;
//Dates from excel: http://blogs.msdn.com/b/eric_carter/archive/2004/08/14/214713.aspx
ExcelCell val = null;
CellFormat toFindNumbFormat = null;
if(c.StyleIndex != null && styles != null)
toFindNumbFormat = styles.CellFormats.ToArray()[c.StyleIndex.Value] as CellFormat;
bool isDate = false;
if (toFindNumbFormat != null && toFindNumbFormat.NumberFormatId.HasValue && toFindNumbFormat.NumberFormatId.Value != 0)
{
var index = toFindNumbFormat.NumberFormatId.Value;
if (index >= 163)
{
NumberingFormat format = styles.NumberingFormats.First(x => x is NumberingFormat && ((NumberingFormat)x).NumberFormatId == toFindNumbFormat.NumberFormatId.Value) as NumberingFormat;
if (format != null && format.FormatCode.HasValue && VerifyDateFormatCode(format.FormatCode.Value))
isDate = true;
}
else if ((index >= 14 && index <= 22) || (index >= 45 && index <= 47))
isDate = true;
}
double doubleVal;
long intVal;
if (c.CellValue == null)
continue;
if (c.DataType != null && c.DataType == CellValues.SharedString)
val = new ExcelCell(dataWithMeta.Item2.SharedStringTable.ElementAt(int.Parse(c.CellValue.Text)).InnerText);
else if (isDate)
val = new ExcelCell(GetDateFromExcelDate(c.CellValue.Text));
else if (c.DataType != null && c.DataType == CellValues.Date)
val = new ExcelCell(DateTime.Parse(c.CellValue.Text));
else if (long.TryParse(c.CellValue.Text, System.Globalization.NumberStyles.Integer, System.Globalization.CultureInfo.InvariantCulture.NumberFormat, out intVal))
val = new ExcelCell(intVal);
else if (double.TryParse(c.CellValue.Text, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.InvariantCulture.NumberFormat, out doubleVal))
val = new ExcelCell(doubleVal);
else
val = new ExcelCell(c.CellValue.Text);
rowData.Add(val);
}
tableData.Add(rowData);
}
return tableData;
}
private static int getColumnCount(ExcelRow row)
{
var highest = 0;
for(int i = 0; i < row.Cells.LastColumnIndex; i++)
{
if(row.Cells[i].Value != null)
highest = i;
}
return highest + 1;
}
private WorkLogDataRow ImportRow(ExcelRow excelRow, SourceFileSettings settings)
{
WorkLogDataRow row = new WorkLogDataRow();
CellRange cells = excelRow.Cells;
row.Key = GetStringValue(cells, settings.KeyField.Position - 1);
row.Title = GetStringValue(cells, settings.TitleField.Position - 1);
row.Assignee = GetStringValue(cells, settings.AssigneeField.Position - 1);
row.Status = GetStringValue(cells, settings.StatusField.Position - 1);
row.WorkDateStr = GetStringValue(cells, settings.DateField.Position - 1);
row.UserName = GetStringValue(cells, settings.UserNameField.Position - 1);
row.Url = GetStringValue(cells, settings.UrlField.Position - 1);
if (settings.EstimationField.IsUseStoryPointsFromMainPosition)
{
row.OriginalEstimateHours = GetStringValue(cells, settings.EstimationField.AlternatePosition - 1);
row.OriginalEstimateStoryPoints = GetStringValue(cells, settings.EstimationField.Position - 1);
}
else
{
row.OriginalEstimateHours = GetStringValue(cells, settings.EstimationField.Position - 1);
row.OriginalEstimateStoryPoints = "0";
}
row.OriginalEstimateHours = RemoveSymbol(row.OriginalEstimateHours, 'h');
row.OriginalEstimateStoryPoints = RemoveSymbol(row.OriginalEstimateStoryPoints, 'h');
row.TimeSpent = RemoveSymbol(GetStringValue(cells, settings.TimeSpentField.Position - 1), 'h');
return row;
}
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;
}
private bool CreateAssetSheet(int group_index, ref DataTable table)
{
bool result = false;
System.Web.UI.HtmlTextWriter _writer = GetWriter();
if ((table != null) && (_writer != null))
{
string expression = "GroupId=" + group_index.ToString();
DataRow[] foundRows = null;
foundRows = table.Select(expression);
ExcelColumns columns = null;
if (foundRows != null)
{
if (foundRows.Length > 0)
{
result = true;
DataRow _init = foundRows[0];
if (_init != null)
{
int _asset_type_id = Int32.Parse(_init["AssetTypeId"].ToString());
int _department_id = Int32.Parse(_init["DepartmentId"].ToString());
columns = CreateColumns(ref columns, ref table);
if (columns != null)
{
DataTable _custom_properties_table = GetAssetProperties(_department_id, _asset_type_id);
if (_custom_properties_table != null)
{
foreach (DataRow _row in _custom_properties_table.Rows)
{
if (_row != null)
{
string _property_name = _row["Name"].ToString();
int _property_type = Int32.Parse(_row["DataType"].ToString());
string _system_type = "System.String";
switch (_property_type)
{
case 0:
_system_type = "System.String";
break;
case 1:
_system_type = "System.Int32";
break;
case 2:
_system_type = "System.Decimal";
break;
case 3:
_system_type = "System.DateTime";
break;
case 4:
_system_type = "System.String";
break;
}
columns = AddColumn(ref columns, _property_name, _system_type);
}
}
}
}
}
}
else
return false;
}
else
return false;
int _sheet_id = group_index + 1;
string _sheet_name = "AssetsGroup" + _sheet_id.ToString();
string _start_worksheet = "<Worksheet ss:Name=\"" + _sheet_name + "\">";
string _end_worksheet = "</Worksheet>";
_writer.Write(_start_worksheet);
ExcelTable _table = this.CreateWorkSheet(_sheet_name, ref columns);
if (_table != null)
{
AddHeader(ref _table, ref columns);
//***new
_writer.Write(_table.ToHeader());
bool is_white = true;
DataTable _custom_properties_full = null;
for (int row_index = 0; row_index < foundRows.Length; row_index++)
{
DataRow _row = foundRows[row_index];
if (_row != null)
{
//***ExcelRow excel_row = AddRow(row_index + 1, ref _table);
ExcelRow excel_row = new ExcelRow(_table);
if (excel_row != null)
{
UpdateRow(ref excel_row, ref table, ref _row, is_white);
int _department = Int32.Parse(_row["DepartmentId"].ToString());
int _asset_id = Int32.Parse(_row["Id"].ToString());
int _asset_type_id = Int32.Parse(_row["AssetTypeId"].ToString());
if (_custom_properties_full == null)
_custom_properties_full = GetAssetProperties(_department, _asset_type_id);
DataTable _custom_properties = GetAssetPropertyValues(_department, _asset_id, _asset_type_id);
UpdateRow(ref excel_row, ref _custom_properties_full, ref _custom_properties, "Name", "PropertyValue", is_white);
if (is_white)
is_white = false;
else
is_white = true;
//***new
_writer.Write(excel_row.ToString());
_writer.Flush();
}
}
}
//***new
_writer.Write(_table.ToFooter());
}
_writer.Write(_end_worksheet);
}
return result;
}
public ExcelRow AddRow(int index)
{
ExcelRow result = null;
if (_rows != null)
{
if (!_rows.Contains(index))
{
ExcelRow _new_row = new ExcelRow(index, this);
if (_new_row != null)
{
_rows.Add(index, _new_row);
result = _new_row;
};
};
};
return result;
}
public void UpdateRow(ref ExcelRow excel_row, ref DataTable all_properties, ref DataTable data_table, string column_name_field, string column_value_field, bool is_white)
{
if ((excel_row != null) && (all_properties != null) && (data_table != null) && (column_name_field.Length > 0) && (column_value_field.Length > 0))
{
foreach (DataRow _row in data_table.Rows)
{
if (_row != null)
{
string _column_name = _row[column_name_field].ToString();
excel_row.SetValue(_column_name, _row[column_value_field].ToString(), is_white);
};
};
foreach (DataRow _allrow in all_properties.Rows)
{
if (_allrow != null)
{
string _property_name = _allrow[column_name_field].ToString();
excel_row.SetValue(_property_name, "", is_white);
};
};
};
}
public void UpdateRow(ref ExcelRow excel_row, ref DataTable data_table, ref DataRow data_row, bool is_white)
{
if ((excel_row != null) && (data_table != null) && (data_row != null))
{
int column_count = data_table.Columns.Count;
for (int column_index = 0; column_index < column_count; column_index++)
{
string _column_name = data_table.Columns[column_index].ColumnName;
if (_column_filter.Length > 0)
{
if (_column_name.ToLower().Contains(_column_filter.ToLower()) && _column_name != "AssetGUID")
continue;
};
excel_row.SetValue(_column_name, data_row[column_index].ToString(), is_white);
};
};
}
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 ExcelCell(int colIndex, int rowIndex, string value)
{
_column = new ExcelColumn(colIndex);
_row = new ExcelRow(rowIndex);
_value = value;
}
