public ExcelWriter()
{
int ticks = Environment.TickCount;
// Create the workbook
Workbook book = new Workbook();
// Set the author
book.Properties.Author = "Anthony Lim";
// Add some style
WorksheetStyle style = book.Styles.Add("style1");
style.Font.Bold = true;
Worksheet sheet = book.Worksheets.Add("SampleSheet");
WorksheetRow Row0 = sheet.Table.Rows.Add();
// Add a cell
Row0.Cells.Add("Hello World", DataType.String, "style1");
// Save it
book.Save(@"./test.xls");
Console.WriteLine("Time:{0}", Environment.TickCount - ticks);
}
public void CreateFlatSheet(DataTable dt)
{
Worksheet flat = book.Worksheets.Add("Flat");
WorksheetRow head = flat.Table.Rows.Add();
foreach (DataColumn col in dt.Columns)
{
head.Cells.Add(col.Caption, DataType.String, this.headings.ID);
}
foreach (DataRow row in dt.Rows)
{
WorksheetRow line = flat.Table.Rows.Add();
for (int i = 0; i < row.ItemArray.Length; i++)
{
//float val; //int du; ValueNumberFormat v = new ValueNumberFormat();
//if (float.TryParse(row[i].ToString(),NumberStyles.Integer,(new ValueNumberFormat()), out val) /*&& Int32.TryParse(row[i].ToString(), NumberStyles.Number,v, out du)*/)
if (i == 8 || i == 6)
{
line.Cells.Add(row[i].ToString(), i == 8 ? DataType.Number : DataType.String, i == 8 ? normal.ID : rightAlign.ID);
}
else
{
line.Cells.Add(row[i].ToString(), DataType.String, normal.ID);
}
}
}
book.Save(this.filename);
}
public void AddWorksheet(DataGridView dataGridView)
{
string cellValue;
DataType cellDataType;
string cellFormat;
string worksheetName = dataGridView.AccessibleName + " - " + GHVHugoLib.Utilities.Account;
if (worksheetName.Length > 31)
{
worksheetName = worksheetName.Substring(0, 31);
}
Worksheet worksheet = m_workbook.Worksheets.Add(worksheetName);
WorksheetRow header = worksheet.Table.Rows.Add();
foreach (DataGridViewColumn column in dataGridView.Columns)
{
header.Cells.Add(new WorksheetCell(column.HeaderText, DataType.String, "Header"));
worksheet.Table.Columns.Add(new WorksheetColumn(column.HeaderText.Length * 7));
}
foreach (DataGridViewRow dataGridViewRow in dataGridView.Rows)
{
WorksheetRow worksheetRow = worksheet.Table.Rows.Add();
foreach (DataGridViewCell dataGridViewCell in dataGridViewRow.Cells)
{
GetCellData(dataGridViewCell, out cellValue, out cellDataType, out cellFormat);
worksheetRow.Cells.Add(new WorksheetCell(cellValue, cellDataType, cellFormat));
}
}
}
public ExcelCreator(DataTable dt)
{
if (string.IsNullOrEmpty(dt.TableName))
{
dt.TableName = "table";
}
book = new Workbook();
Worksheet sheet = book.Worksheets.Add(dt.TableName ?? "Worksheet 1");
WorksheetRow wRow = sheet.Table.Rows.Add();
foreach (DataColumn col in dt.Columns)
{
wRow.Cells.Add(col.Caption);
}
foreach (DataRow row in dt.Rows)
{
wRow = sheet.Table.Rows.Add();
foreach (object cell in row.ItemArray)
{
string cellValue = (cell.GetType().Equals(typeof(System.DBNull))) ? string.Empty : (string)cell;
wRow.Cells.Add(cellValue);
}
}
}
public void AddWorksheet(DataTable dataTable)
{
string cellValue;
DataType cellDataType;
string cellFormat;
string worksheetName = dataTable.TableName;
if (worksheetName.Length > 31)
{
worksheetName = worksheetName.Substring(0, 31);
}
Worksheet worksheet = m_workbook.Worksheets.Add(worksheetName);
WorksheetRow header = worksheet.Table.Rows.Add();
foreach (DataColumn dataColumn in dataTable.Columns)
{
header.Cells.Add(new WorksheetCell(dataColumn.ColumnName, DataType.String, "Header"));
worksheet.Table.Columns.Add(new WorksheetColumn(dataColumn.ColumnName.Length * 7));
}
foreach (DataRow dataRow in dataTable.Rows)
{
WorksheetRow worksheetRow = worksheet.Table.Rows.Add();
int column = 0;
foreach (object dataCell in dataRow.ItemArray)
{
Type dataType = dataTable.Columns[column].DataType;
GetCellData(dataCell, dataType, out cellValue, out cellDataType, out cellFormat);
worksheetRow.Cells.Add(new WorksheetCell(cellValue, cellDataType, cellFormat));
column++;
}
}
}
private void sfdExportExcel_FileOk(object sender, CancelEventArgs e)
{
Workbook xlsBook = new Workbook();
Worksheet xlsSheet = xlsBook.Worksheets.Add("EmployeeeList");
WorksheetRow xlsRow = xlsSheet.Table.Rows.Add();
xlsRow.Cells.Add("Leave Code");
xlsRow.Cells.Add("Status");
xlsRow.Cells.Add("Requestor");
xlsRow.Cells.Add("Leave Type");
xlsRow.Cells.Add("Date File");
xlsRow.Cells.Add("Date Start");
xlsRow.Cells.Add("Date End");
xlsRow.Cells.Add("Unit");
xlsRow.Cells.Add("Approver");
xlsRow.Cells.Add("Reason");
foreach (DataGridViewRow drw in dgLeaveList.Rows)
{
xlsRow = xlsSheet.Table.Rows.Add();
xlsRow.Cells.Add(drw.Cells[0].Value.ToString());
xlsRow.Cells.Add(drw.Cells[1].Value.ToString());
xlsRow.Cells.Add(drw.Cells[2].Value.ToString());
xlsRow.Cells.Add(drw.Cells[3].Value.ToString());
xlsRow.Cells.Add(drw.Cells[4].Value.ToString());
xlsRow.Cells.Add(drw.Cells[5].Value.ToString());
xlsRow.Cells.Add(drw.Cells[6].Value.ToString());
xlsRow.Cells.Add(drw.Cells[7].Value.ToString());
xlsRow.Cells.Add(drw.Cells[8].Value.ToString());
xlsRow.Cells.Add(drw.Cells[9].Value.ToString());
}
xlsBook.Save(sfdExportExcel.FileName);
}
public ExcelLogFile(DataGridView dg, string logFolder)
{
this.logFolder = logFolder;
book = new Workbook();
fullSatistic = book.Worksheets.Add("Full");
row = fullSatistic.Table.Rows.Add();
this.DG = dg;
HeadLine = book.Styles.Add("HeadLine");
HeadLine.Font.Size = 14;
HeadLine.Font.Bold = true;
HeadLine.Alignment.Horizontal = StyleHorizontalAlignment.Center;
Fail = book.Styles.Add("Fail");
Fail.Font.Size = 10;
Fail.Font.Color = "Red";
Fail.Font.Bold = true;
Fail.Alignment.Horizontal = StyleHorizontalAlignment.Center;
Pass = book.Styles.Add("Pass");
Pass.Font.Size = 10;
Pass.Font.Color = "Green";
Pass.Font.Bold = true;
Pass.Alignment.Horizontal = StyleHorizontalAlignment.Center;
Defult = book.Styles.Add("Default");
Defult.Font.Size = 10;
Defult.Font.Bold = true;
Defult.Alignment.Horizontal = StyleHorizontalAlignment.Center;
CerateLogFile();
NewLine();
}
/// <summary>
/// Export the given DataSet to an Excel XML object and send to the HTTP output
/// </summary>
/// <param name="ds"></param>
public void ExportDataSetToXMLExcel(DataSet ds)
{
Workbook book = new Workbook();
foreach (DataTable dt in ds.Tables)
{
Worksheet sheet = book.Worksheets.Add(dt.TableName);
// Add Header Row
WorksheetRow headerRow = sheet.Table.Rows.Add();
for (int i = 0; i < dt.Columns.Count; i++)
{
headerRow.Cells.Add(new WorksheetCell(dt.Columns[i].ColumnName));
}
// Add DataTable Rows
foreach (DataRow dr in dt.Rows)
{
WorksheetRow row = sheet.Table.Rows.Add();
object[] a = dr.ItemArray;
for (int i = 0; i < a.Length; i++)
{
row.Cells.Add(new WorksheetCell(a[i].ToString(), GetDataType(a[i].GetType().Name)));
}
}
}
book.Save(HttpContext.Current.Response.OutputStream);
}
private void sfdExportExcel_FileOk(object sender, CancelEventArgs e)
{
Workbook xlsBook = new Workbook();
Worksheet xlsSheet = xlsBook.Worksheets.Add("EmployeeeList");
WorksheetRow xlsRow = xlsSheet.Table.Rows.Add();
xlsRow.Cells.Add("Number");
xlsRow.Cells.Add("Last Name");
xlsRow.Cells.Add("First Name");
xlsRow.Cells.Add("JG Code");
xlsRow.Cells.Add("Employee Type");
xlsRow.Cells.Add("Employment Status");
xlsRow.Cells.Add("Division");
xlsRow.Cells.Add("Group");
xlsRow.Cells.Add("Department");
foreach (DataGridViewRow drw in dgEmployeeList.Rows)
{
xlsRow = xlsSheet.Table.Rows.Add();
xlsRow.Cells.Add(drw.Cells[1].Value.ToString());
xlsRow.Cells.Add(drw.Cells[2].Value.ToString());
xlsRow.Cells.Add(drw.Cells[3].Value.ToString());
xlsRow.Cells.Add(drw.Cells[4].Value.ToString());
xlsRow.Cells.Add(drw.Cells[5].Value.ToString());
xlsRow.Cells.Add(drw.Cells[6].Value.ToString());
xlsRow.Cells.Add(drw.Cells[7].Value.ToString());
xlsRow.Cells.Add(drw.Cells[8].Value.ToString());
xlsRow.Cells.Add(drw.Cells[9].Value.ToString());
}
xlsBook.Save(sfdExportExcel.FileName);
}
public void Parse(string filename)
{
if (File.Exists(filename))
{
Workbook book = new Workbook();
if (File.Exists(countriesXLSFilename))
{
book.Load(countriesXLSFilename);
}
// get just the last name without the extension
string[] tokens = filename.Split(new char[] { '\\', '.' });
string sheetName = tokens[tokens.Length - 2];
Worksheet sheet = book.Worksheets.Add(sheetName);
StreamReader sr = File.OpenText(filename);
string line = "";
while ((line = sr.ReadLine()) != null)
{
string country;
string value;
// remove dollar signs
line = line.Replace("$", "");
GetData(line, out country, out value);
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add(country);
row.Cells.Add(value);
}
book.Save(countriesXLSFilename);
}
}
private void GenMetadataSpreadsheetRow
(Worksheet sheet, string legend, string value)
{
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add(legend);
row.Cells.Add(value);
}
static void Main(string[] args)
{
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets.Add("Sample");
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add("Hello World");
book.Save(@"c:\test.xls");
}
public void LogKeyStroke(DateTime time, string key, string modifier)
{
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add(this.GetTime(time).ToString());
row.Cells.Add("Key");
row.Cells.Add(key);
row.Cells.Add(modifier);
}
/// <summary>
/// Write the header of the report.
/// </summary>
public override void WriteHeader()
{
WorksheetRow wr = this.ws.Table.Rows.Add();
for (int i = 0; i < OneDayInfo.Header.Count; i++)
{
wr.Cells.Add(OneDayInfo.Header[i]);
}
}
public virtual void RemoveWorkshetRow(long id, string SessionId)
{
WorksheetContext ctx = Session.GetSessionData(SessionId, "WorksheetContext").CastToType <WorksheetContext>();
WorksheetRow row = ctx.WorksheetRows.FirstOrDefulatEntity(r => r.Id == id);
if (row.IsNotNull())
{
ctx.WorksheetRows.Remove(row);
}
}
private void ViewKeyDown(object sender, KeyEventArgs e)
{
if (e.KeyData == Keys.Delete)
{
WorksheetRow row = gridView.GetFocusedRow() as WorksheetRow;
if (row.IsNotNull())
{
this.InvokeActionMethod("Screening", "RemoveWorkshetRow", new { id = row.Id, SessionId = SessionId });
}
}
}
public static void Test()
{
string numefisier = string.Concat("C:\\COSTEA\\", DateTime.Now.ToString("yyyyMMddHHmmss"), ".xls");
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets.Add("iDava");
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add("Hello World");
book.Save(numefisier);
Process.Start(numefisier);
}
public void LogMousePosition(DateTime time, int x, int y)
{
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add(this.GetTime(time).ToString());
row.Cells.Add("Move");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add(x.ToString());
row.Cells.Add(y.ToString());
}
public void Create()
{
Worksheet matrix = book.Worksheets.Add("Matrix");
foreach (DataRow row in this.tblAccounts.Rows)
{
WorksheetRow head = matrix.Table.Rows.Add();
foreach (CellFieldProperty h in this.__headers)
{
head.Cells.Add(h.FieldName, DataType.String, this.coloredHeading.ID);
head.Cells.Add(h.getFormatted(row), h.dataType, normal.ID);
}
WorksheetRow detHead = matrix.Table.Rows.Add();
foreach (CellFieldProperty d in this.__details)
{
detHead.Cells.Add(d.FieldName, DataType.String, headings.ID);
}
float totals = 0.00f;
DataRowCollection details = Program.getLoadsDetails(row[0].ToString());
foreach (DataRow detail in details)
{
WorksheetRow det = matrix.Table.Rows.Add();
foreach (CellFieldProperty d in this.__details)
{
det.Cells.Add(d.getFormatted(detail), d.dataType, normal.ID);
if (d.dataType == DataType.Number || d.dataType == DataType.Integer)
{
totals += getNumericEquivalent(detail[d.FieldName].ToString());
}
}
}
WorksheetRow RowTotal = matrix.Table.Rows.Add();
foreach (CellFieldProperty f in this.__details)
{
if (f.dataType == DataType.Integer || f.dataType == DataType.Number)
{
RowTotal.Cells.Add(totals.ToString("0.####"), f.dataType, headings.ID);
}
else
{
RowTotal.Cells.Add();
}
}
matrix.Table.Rows.Add();
}
book.Save(this.filename);
}
public static Workbook Generate64DaysDestoryReport(IList <ClaimOrder> claimOrders)
{
Workbook workbook = new Workbook(WorkbookFormat.Excel2007);
Worksheet worksheet = workbook.Worksheets.Add("报表");
WorksheetRow titleRow = worksheet.Rows[0];
int titleCellidx = 0;
titleRow.Cells[titleCellidx++].Value = "RTV";
titleRow.Cells[titleCellidx++].Value = "到HRTV仓库日期";
titleRow.Cells[titleCellidx++].Value = "店号";
// titleRow.Cells[titleCellidx++].Value = "批次";
// titleRow.Cells[titleCellidx++].Value = "序号";
titleRow.Cells[titleCellidx++].Value = "索赔号";
titleRow.Cells[titleCellidx++].Value = "供应商号";
titleRow.Cells[titleCellidx++].Value = "供应商名称";
titleRow.Cells[titleCellidx++].Value = "部门";
titleRow.Cells[titleCellidx++].Value = "定案日期";
titleRow.Cells[titleCellidx++].Value = "箱数";
titleRow.Cells[titleCellidx++].Value = "件数";
titleRow.Cells[titleCellidx++].Value = "金额";
titleRow.Cells[titleCellidx++].Value = "索赔原因";
titleRow.Cells[titleCellidx].Value = "通知天数";
titleRow.CellFormat.FillPattern = FillPatternStyle.Solid;
titleRow.CellFormat.FillPatternForegroundColor = Color.AntiqueWhite;
int contentRowIdx = 1;
foreach (ClaimOrder claimOrder in claimOrders)
{
WorksheetRow contentRow = worksheet.Rows[contentRowIdx++];
int contentCellIdx = 0;
contentRow.Cells[contentCellIdx++].Value = claimOrder.rtv;
contentRow.Cells[contentCellIdx++].Value = claimOrder.arriveRTVDate;
contentRow.Cells[contentCellIdx++].Value = claimOrder.storeNo;
// contentRow.Cells[contentCellIdx++].Value = claimOrder.lotNo;
// contentRow.Cells[contentCellIdx++].Value = 0;
contentRow.Cells[contentCellIdx++].Value = claimOrder.claimNo;
contentRow.Cells[contentCellIdx++].Value = claimOrder.supplierNo;
contentRow.Cells[contentCellIdx++].Value = claimOrder.supplierName;
contentRow.Cells[contentCellIdx++].Value = claimOrder.dept;
contentRow.Cells[contentCellIdx++].Value = claimOrder.decidedDate;
contentRow.Cells[contentCellIdx++].Value = claimOrder.qty;
contentRow.Cells[contentCellIdx++].Value = claimOrder.pcs;
contentRow.Cells[contentCellIdx++].Value = claimOrder.claimAmount;
contentRow.Cells[contentCellIdx++].Value = claimOrder.claimReason;
contentRow.Cells[contentCellIdx].Value = claimOrder.informDays;
}
return(workbook);
}
/// <summary>
/// Fülle das Calc - Sheet mit Daten
/// </summary>
public void toXLS()
{
WorksheetRow row = makeRow();
row.Height = 20;
for (int i = 0; i <= this.dataLine.Count - 1; i++)
{
//row.Index = i;
row.Height = 20;
row.Cells.Add(new WorksheetCell(this.dataLine[i].ToString(), "ce11"));
}
this.dataLine.Clear();
}
public bool MoveNext()
{
currentWorksheetRow = rows[++index];
for (int i = 0; i < cellIndexTitleDict.Length; i++)
{
if (currentWorksheetRow.Cells[i].Value != null)
{
return(true);
}
}
return(false);
}
public void LogTaskSwitch(string newTask)
{
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add("");
row.Cells.Add("Task Switch");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add(newTask);
}
public void LogTaskBreakDown(string taskBreakdown)
{
WorksheetRow row = sheet.Table.Rows.Add();
row.Cells.Add("");
row.Cells.Add("Task Breakdown");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add("");
row.Cells.Add(taskBreakdown);
}
/// <summary>
/// 将Excel转化为DataSet
/// </summary>
public static DataSet ExcelToDataSet(string fileFullPath)
{
DataSet dsExcel = new DataSet();
DataTable dt = new DataTable();
Workbook workBook = Workbook.Load(fileFullPath);
Worksheet workSheet = workBook.Worksheets[0];
int maxRowCount = Workbook.MaxExcelRowCount;
int maxColumnCount = Workbook.MaxExcelColumnCount;
if (workSheet != null)
{
//根据第一行创建DataTable Column
WorksheetRow firstRow = workSheet.Rows[0];
int columnCount = 0;
for (int i = 0; i < maxColumnCount; i++)
{
if (firstRow.Cells[i].Value != null)
{
dt.Columns.Add(Convert.ToString(firstRow.Cells[i].Value));
columnCount = columnCount + 1;
}
else
{
break;
}
}
//加载数据
for (int j = 1; j < maxRowCount; j++)
{
WorksheetRow dataRow = workSheet.Rows[j];
if (dataRow.Cells[0].Value == null && dataRow.Cells[columnCount - 1].Value == null)
{
break;
}
DataRow dr = dt.NewRow();
for (int k = 0; k < columnCount; k++)
{
dr[k] = dataRow.Cells[k].Value;
}
dt.Rows.Add(dr);
}
}
dsExcel.Tables.Add(dt);
return(dsExcel);
}
private void GenerateHeaders(Worksheet sheet)
{
// -----------------------------------------------
WorksheetRow Row0 = sheet.Table.Rows.Add();
Row0.Height = 25;
Row0.AutoFitHeight = false;
WorksheetCell cell;
cell = Row0.Cells.Add();
cell.StyleID = "s63";
cell.Data.Type = DataType.String;
cell.Data.Text = "רשימת קניות";
cell.MergeAcross = 5;
cell.NamedCell.Add("Print_Area");
// -----------------------------------------------
WorksheetRow Row1 = sheet.Table.Rows.Add();
Row1.Height = 15;
Row1.AutoFitHeight = false;
cell = Row1.Cells.Add();
cell.StyleID = "s64";
cell.NamedCell.Add("Print_Area");
cell = Row1.Cells.Add();
cell.StyleID = "s64";
cell.Data.Type = DataType.String;
cell.Data.Text = "שם המוצר";
cell.NamedCell.Add("Print_Area");
cell = Row1.Cells.Add();
cell.StyleID = "s64";
cell.Data.Type = DataType.String;
cell.Data.Text = "כמות";
cell.NamedCell.Add("Print_Area");
cell = Row1.Cells.Add();
cell.StyleID = "s64";
cell.Data.Type = DataType.String;
cell.Data.Text = "מחיר";
cell.NamedCell.Add("Print_Area");
cell = Row1.Cells.Add();
cell.StyleID = "s64";
cell.Data.Type = DataType.String;
cell.Data.Text = "הערות";
cell.NamedCell.Add("Print_Area");
cell = Row1.Cells.Add();
cell.StyleID = "s64";
cell.Data.Type = DataType.String;
cell.Data.Text = "סה\"כ";
cell.NamedCell.Add("Print_Area");
// -----------------------------------------------
}
/// <summary>
/// Write an item of the report.
/// </summary>
/// <param name="day">One day info.</param>
public override void WriteDay(OneDayInfo day)
{
WorksheetRow wr = this.ws.Table.Rows.Add();
wr.Cells.Add(new WorksheetCell(this.D(day.Date), DataType.String, "dateStyle"));
wr.Cells.Add(new WorksheetCell(this.B(day.IsMentruation), DataType.String));
wr.Cells.Add(new WorksheetCell(this.I(day.Egesta, day.IsMentruation), day.IsMentruation ? DataType.Number : DataType.String));
wr.Cells.Add(new WorksheetCell(this.B(day.IsOvulation), DataType.String));
wr.Cells.Add(new WorksheetCell(this.B(day.HadSex), DataType.String));
wr.Cells.Add(new WorksheetCell(this.D(day.BBT), day.BBT != 0 ? DataType.Number : DataType.String, day.BBT != 0 ? "floatStyle" : "Default"));
wr.Cells.Add(new WorksheetCell(this.I(day.Health), DataType.Number));
wr.Cells.Add(new WorksheetCell(this.CF(day.CF), DataType.String));
wr.Cells.Add(new WorksheetCell(day.Note, DataType.String));
}
/// <summary>
/// Metodo per la generazione del contenuto del report
/// </summary>
/// <param name="sheet">Foglio a cui aggiungere i dati</param>
/// <param name="contentRows">Lista delle righe</param>
private void AddContent(Worksheet sheet, List <ReportMapRowProperty> contentRows)
{
// Generazione delle righe
foreach (var row in contentRows)
{
WorksheetRow worksheetRow = sheet.Table.Rows.Add();
// Compilazione della riga
foreach (var column in row.Columns)
{
//worksheetRow.Cells.Add(column.Value, String.IsNullOrEmpty(column.Value) ? DataType.String : this.GetDataType(column.ContantDataType), StylesEnum.ContentStyle.ToString());
worksheetRow.Cells.Add(column.Value, DataType.String, StylesEnum.ContentStyle.ToString());
}
}
}
private void sfdExportExcel_FileOk(object sender, CancelEventArgs e)
{
Workbook xlsBook = new Workbook();
Worksheet xlsSheet = xlsBook.Worksheets.Add("Timesheet");
WorksheetRow xlsRow = xlsSheet.Table.Rows.Add();
xlsRow.Cells.Add("EMPLOYEE NAME:");
xlsRow.Cells.Add();
xlsRow.Cells.Add(txtName.Text);
xlsRow = xlsSheet.Table.Rows.Add();
xlsRow = xlsSheet.Table.Rows.Add();
xlsRow.Cells.Add("Status");
xlsRow.Cells.Add("Date");
xlsRow.Cells.Add("Shift");
xlsRow.Cells.Add("Time In");
xlsRow.Cells.Add("Time Out");
xlsRow.Cells.Add("Schedule In");
xlsRow.Cells.Add("Schedule Out");
xlsRow.Cells.Add("Total");
xlsRow.Cells.Add("Work");
xlsRow.Cells.Add("Absent");
xlsRow.Cells.Add("LWP");
xlsRow.Cells.Add("LWOP");
xlsRow.Cells.Add("Late");
xlsRow.Cells.Add("Undertime");
foreach (DataGridViewRow drw in dgvTimeSheet.Rows)
{
xlsRow = xlsSheet.Table.Rows.Add();
xlsRow.Cells.Add(drw.Cells[0].Value.ToString());
xlsRow.Cells.Add(drw.Cells[1].Value.ToString());
xlsRow.Cells.Add(drw.Cells[2].Value.ToString());
xlsRow.Cells.Add(drw.Cells[3].Value.ToString());
xlsRow.Cells.Add(drw.Cells[4].Value.ToString());
xlsRow.Cells.Add(drw.Cells[5].Value.ToString());
xlsRow.Cells.Add(drw.Cells[6].Value.ToString());
xlsRow.Cells.Add(drw.Cells[7].Value.ToString());
xlsRow.Cells.Add(drw.Cells[8].Value.ToString());
xlsRow.Cells.Add(drw.Cells[9].Value.ToString());
xlsRow.Cells.Add(drw.Cells[10].Value.ToString());
xlsRow.Cells.Add(drw.Cells[11].Value.ToString());
xlsRow.Cells.Add(drw.Cells[12].Value.ToString());
xlsRow.Cells.Add(drw.Cells[13].Value.ToString());
}
xlsBook.Save(sfdExportExcel.FileName);
}
public void Export(EnumerableRowCollection <DataLogModel.ImpactsRow> impacts)
{
Workbook workbook = new Workbook();
workbook.Properties.Title = "LaJust Sports Export";
workbook.Properties.Created = DateTime.Now;
Worksheet impactSheet = workbook.Worksheets.Add("Impacts");
WorksheetRow row = impactSheet.Table.Rows.Add();
row.Cells.Add(new WorksheetCell("Sequence", DataType.String));
row.Cells.Add(new WorksheetCell("Time", DataType.String));
row.Cells.Add(new WorksheetCell("Device ID", DataType.String));
row.Cells.Add(new WorksheetCell("Game Number", DataType.String));
row.Cells.Add(new WorksheetCell("Round Number", DataType.String));
row.Cells.Add(new WorksheetCell("Seconds", DataType.String));
row.Cells.Add(new WorksheetCell("Competitor", DataType.String));
row.Cells.Add(new WorksheetCell("Data Source", DataType.String));
row.Cells.Add(new WorksheetCell("Required Impact", DataType.String));
row.Cells.Add(new WorksheetCell("Actual Impact", DataType.String));
row.Cells.Add(new WorksheetCell("Panel", DataType.String));
foreach (var impactRow in impacts)
{
row = impactSheet.Table.Rows.Add();
row.Cells.Add(new WorksheetCell(impactRow.ID.ToString(), DataType.Number));
row.Cells.Add(new WorksheetCell(impactRow.Timestamp.ToString("G"), DataType.String));
row.Cells.Add(new WorksheetCell(impactRow.SensorId.ToString(), DataType.String));
row.Cells.Add(new WorksheetCell(impactRow.GameNumber.ToString(), DataType.Number));
row.Cells.Add(new WorksheetCell(impactRow.RoundNumber.ToString(), DataType.Number));
row.Cells.Add(new WorksheetCell(impactRow.ElapsedTime.TotalSeconds.ToString(), DataType.Number));
row.Cells.Add(new WorksheetCell(impactRow.Name.ToString(), DataType.String));
row.Cells.Add(new WorksheetCell(impactRow.DataSource.ToString(), DataType.String));
row.Cells.Add(new WorksheetCell(impactRow.RequiredLevel.ToString(), DataType.Number));
row.Cells.Add(new WorksheetCell(impactRow.ImpactLevel.ToString(), DataType.Number));
row.Cells.Add(new WorksheetCell(impactRow.SensorPanel.ToString(), DataType.String));
}
string filename = string.Format("{0} {1} G{2:D3}-{3}R.xls",
impacts.First().Timestamp.ToString("yyyy-MM-dd"),
impacts.First().Name,
impacts.First().GameNumber,
impacts.First().RoundNumber);
workbook.Save(Path.Combine(GetDataDirectory(), filename));
}
/// <summary>
/// Exports a grid field caption (header or footer) to the specified worksheet cell.
///
/// </summary>
protected virtual void ExportGridFieldCaption(WebDataGrid grid, GridFieldCaption caption, WorksheetRow wsRow, WorksheetCell wsCell, int rowIndex, int columnIndex, string gridCssClass, string captionRowCssClass, bool isHeader, int rowSpan, int colSpan, int colOffset)
{
WorksheetMergedCellsRegion mergedCellsRegion = rowSpan > 1 || colSpan > 1 ? wsRow.Worksheet.MergedCellsRegions.Add(rowIndex, columnIndex + colOffset, rowIndex + rowSpan - 1, columnIndex + colOffset + colSpan - 1) : (WorksheetMergedCellsRegion)null;
ExcelCellExportingEventArgs e1 = new ExcelCellExportingEventArgs(wsRow.Worksheet, wsCell, rowIndex, columnIndex, wsRow.OutlineLevel, isHeader, !isHeader, false);
this.OnCellExporting(e1);
if (e1.Cancel)
return;
GridFieldCaptionExportingEventArgs e2 = new GridFieldCaptionExportingEventArgs(caption, wsRow.Worksheet, wsCell, rowIndex, columnIndex, wsRow.OutlineLevel, isHeader, !isHeader, false);
this.OnGridFieldCaptionExporting(e2);
if (e2.Cancel)
return;
if (mergedCellsRegion != null)
mergedCellsRegion.Value = (object)caption.Text;
else
wsCell.Value = (object)caption.Text;
if (this.EnableStylesExport)
{
string styleClassString = grid.RunBot.StyleBot.GetStyleClassString((int)caption.Role, caption.CssClassResolved);
if (caption.OwnerField is GroupField && caption.Role == WebDataGridRoles.HeaderCaption)
styleClassString = grid.RunBot.StyleBot.GetStyleClassString(10, styleClassString);
CssSelector selector1 = new CssSelector();
selector1.AddClasses(string.Format("{0} {1} {2}", (object)gridCssClass, (object)captionRowCssClass, (object)styleClassString));
CssStyle styleObject1 = this._currentStyleSheet.GetStyleObject(selector1);
this.ApplyCellFormatFromStyle(wsCell.Worksheet.Workbook, wsCell.CellFormat, styleObject1, grid);
if (mergedCellsRegion != null)
this.ApplyCellFormatFromStyle(mergedCellsRegion.Worksheet.Workbook, mergedCellsRegion.CellFormat, styleObject1, grid);
CssSelector selector2 = new CssSelector();
selector2.AddClasses(styleClassString);
CssStyle styleObject2 = this._currentStyleSheet.GetStyleObject(selector2);
this.ApplyCellBorderFromStyle(wsCell.CellFormat, styleObject2);
if (mergedCellsRegion != null)
this.ApplyCellBorderFromStyle(mergedCellsRegion.CellFormat, styleObject2);
}
if (wsCell.Value != null)
{
this.SetColumnWidth(wsCell.Worksheet.Columns[wsCell.ColumnIndex], wsCell);
if (wsCell.Value.ToString().Contains(Environment.NewLine))
wsCell.CellFormat.WrapText = ExcelDefaultableBoolean.True;
}
if (mergedCellsRegion != null && mergedCellsRegion.Value != null && mergedCellsRegion.Value.ToString().Contains(Environment.NewLine))
mergedCellsRegion.CellFormat.WrapText = ExcelDefaultableBoolean.True;
this.OnGridFieldCaptionExported(new GridFieldCaptionExportedEventArgs(caption, wsRow.Worksheet, wsCell, rowIndex, columnIndex, wsRow.OutlineLevel, isHeader, !isHeader, false));
this.OnCellExported(new ExcelCellExportedEventArgs(wsRow.Worksheet, wsCell, rowIndex, columnIndex, wsRow.OutlineLevel, isHeader, !isHeader, false));
}
/// <summary>
/// Exports a grid record item to its corresponding worksheet cell.
///
/// </summary>
protected virtual void ExportCell(GridRecordItem item, WorksheetRow worksheetRow, int rowIndex, int columnIndex, string gridCssClass, string itemCssClass)
{
WorksheetCell worksheetCell = worksheetRow.Cells[columnIndex];
ExcelCellExportingEventArgs e1 = new ExcelCellExportingEventArgs(worksheetRow.Worksheet, worksheetCell, rowIndex, columnIndex, worksheetRow.OutlineLevel, false, false, false);
this.OnCellExporting(e1);
if (e1.Cancel)
return;
GridRecordItemExportingEventArgs e2 = new GridRecordItemExportingEventArgs(item, worksheetRow.Worksheet, worksheetCell, rowIndex, columnIndex, worksheetRow.OutlineLevel, false, false, false);
this.OnGridRecordItemExporting(e2);
if (e2.Cancel)
return;
worksheetCell.Value = !item.HasTemplate ? (!(item.Column is FormattedGridField) || this.DisableCellValueFormatting || string.IsNullOrEmpty(((FormattedGridField)item.Column).DataFormatString) ? item.Value : (object)string.Format(((FormattedGridField)item.Column).DataFormatString, item.Value)) : (object)this.RenderTemplate(item.TemplateContainer);
if (this.EnableStylesExport)
{
if (!string.IsNullOrEmpty(item.CssClass))
itemCssClass = itemCssClass + " " + item.CssClass;
if (!string.IsNullOrEmpty(item.Column.CssClass))
itemCssClass = itemCssClass + " " + item.Column.CssClass;
CssSelector selector1 = new CssSelector();
selector1.AddClasses(string.Format("{0} {1}", (object)gridCssClass, (object)itemCssClass));
CssStyle styleObject1 = this._currentStyleSheet.GetStyleObject(selector1);
this.ApplyCellFormatFromStyle(worksheetCell.Worksheet.Workbook, worksheetCell.CellFormat, styleObject1, (WebDataGrid)item.Row.ControlMain);
CssSelector selector2 = new CssSelector();
selector2.AddClasses(itemCssClass);
CssStyle styleObject2 = this._currentStyleSheet.GetStyleObject(selector2);
this.ApplyCellBorderFromStyle(worksheetCell.CellFormat, styleObject2);
}
if (worksheetCell.Value != null)
{
this.SetColumnWidth(worksheetRow.Worksheet.Columns[columnIndex], worksheetCell);
if (worksheetCell.Value.ToString().Contains(Environment.NewLine))
worksheetCell.CellFormat.WrapText = ExcelDefaultableBoolean.True;
}
this.OnGridRecordItemExported(new GridRecordItemExportedEventArgs(item, worksheetRow.Worksheet, worksheetCell, rowIndex, columnIndex, worksheetRow.OutlineLevel, false, false, false));
this.OnCellExported(new ExcelCellExportedEventArgs(worksheetRow.Worksheet, worksheetCell, rowIndex, columnIndex, worksheetRow.OutlineLevel, false, false, false));
}
