public void Save(string saveFile)
{
workbook.SaveAs(saveFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing);
string fileTxt = saveFile.Replace(".XLSX", ".TXT");
Worksheet tempWorksheet = (Worksheet)workbook.Sheets.get_Item(1);
worksheet.SaveAs(fileTxt, XlFileFormat.xlCSV, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing);
}
// Export DataTable into an excel file with field names in the header line
// - Save excel file without ever making it visible if filepath is given
// - Don't save excel file, just make it visible if no filepath is given
public static void ExportToExcel(this sys.DataTable Tbl, string ExcelFilePath = null)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
Application excelApp = new Application();
excelApp.Workbooks.Add();
// single worksheet
_Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
//MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public static bool GenExcel <T>(string filepath, string sheetName, string[] headers, string[] pathes, List <T> items)
{
Application excel = new Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;
excel.DisplayAlerts = false;
Workbooks books = excel.Workbooks;
_Workbook book = books.Add(XlWBATemplate.xlWBATWorksheet);
_Worksheet sheet = book.ActiveSheet;
try
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
sheet.Name = sheetName;
for (int i = 0; i < headers.Length; i++)
{
excel.Cells[1, i + 1] = headers[i];
}
Type t = typeof(T);
for (int i = 0; i < items.Count; i++)
{
for (int j = 0; j < pathes.Length; j++)
{
object v = t.GetProperty(pathes[j]).GetValue(items[i], null);
excel.Cells[2 + i, j + 1] = v == null ? "" : v.ToString();
}
}
Range range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[items.Count + 1, headers.Length + 1]];
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.SaveAs(filepath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
}
catch (Exception e)
{
return(false);
}
finally
{
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
Marshal.ReleaseComObject(excel);
GC.Collect();
}
return(true);
}
/// <summary>
/// 이 Worksheet를 다른 이름으로 저장합니다.
/// </summary>
/// <param name="fileName">저장할 파일 이름.</param>
/// <param name="fileFormat">파일 형식.</param>
public void SaveAs(string fileName, XlFileFormat fileFormat)
{
_xlSheet.SaveAs(
fileName, // Filename
fileFormat, // FileFormat
Type.Missing, // Password
Type.Missing, // WriteResPassword
Type.Missing, // ReadOnlyRecommended
Type.Missing, // CreateBackup
Type.Missing, // AddToMru
Type.Missing, // TextCodepage
Type.Missing, // TextVisualLayout
Type.Missing // Local
);
}
public void SaveEndOfBase(List <ComModel> comModels, string name, string group)
{
int j = 0;
ComModel [] arr = comModels.ToArray();
// Загрузить Excel, затем создать новую пустую рабочую книгу
Application excelApp = new Application();
// Сделать приложение Excel видимым
excelApp.Visible = true;
excelApp.Workbooks.Add();
_Worksheet workSheet = excelApp.ActiveSheet;
// Установить заголовки столбцов в ячейках
workSheet.Cells[1, 1] = "com_1";
workSheet.Cells[1, 2] = "com_2";
workSheet.Cells[1, 3] = "com_3";
workSheet.Cells[1, 4] = "com_4";
workSheet.Cells[1, 5] = "com_5";
workSheet.Cells[1, 6] = "com_6";
workSheet.Cells[1, 7] = "com_7";
workSheet.Cells[1, 8] = "com_8";
for (int i = 2; i < comModels.Count; i++)
{
workSheet.Cells[i, 1] = arr[j].Com_1;
workSheet.Cells[i, 2] = arr[j].Com_2.ToString();
workSheet.Cells[i, 3] = arr[j].Com_3.ToString();
workSheet.Cells[i, 4] = arr[j].Com_4.ToString();
workSheet.Cells[i, 5] = arr[j].Com_5.ToString();
workSheet.Cells[i, 6] = arr[j].Com_6.ToString();
workSheet.Cells[i, 7] = arr[j].Com_7.ToString();
workSheet.Cells[i, 8] = arr[j].Com_8.ToString();
j++;
}
excelApp.DisplayAlerts = false;
workSheet.SaveAs(string.Format(@"{0}\" + $"{name}-" + $"{group}" + ".xlsx", Environment.CurrentDirectory));
}
static void ExportToExcel(List <Car> carsInStock)
{
// 加载Excel,创建一个新的空的工作簿
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
// 使Excel在计算机中可见
excelApp.Visible = true;
excelApp.Workbooks.Add();
// 本示例需要一个工作表
_Worksheet workSheet = excelApp.ActiveSheet;
// 在单元格中创建列头
workSheet.Cells[1, "A"] = "Make";
workSheet.Cells[1, "B"] = "Color";
workSheet.Cells[1, "C"] = "PetName";
// 现在,将所有List<Car>中的数据映射到工作表中
int row = 1;
foreach (Car c in carsInStock)
{
row++;
workSheet.Cells[row, "A"] = c.Make;
workSheet.Cells[row, "B"] = c.Color;
workSheet.Cells[row, "C"] = c.PetName;
}
// 美化表数据
workSheet.Range["A1"].AutoFormat(XlRangeAutoFormat.xlRangeAutoFormatClassic2);
// 保存文件,退出Excel并将信息显示给客户
workSheet.SaveAs(string.Format(@"{0}\Inventory.xlsx", Environment.CurrentDirectory));
excelApp.Quit();
MessageBox.Show("The Inventory.xlsx file has been saved to your app floder", "Export complete");
}
static void ExportToExcel(List <Car> carsInStock)
{
// Load up Excel, then make a new empty workbook.
Application excelApp = new Application();
excelApp.Workbooks.Add();
// Go ahead and make Excel visible on the computer.
excelApp.Visible = true;
// This example uses a single worksheet.
_Worksheet workSheet = excelApp.ActiveSheet;
// Establish column headings in cells.
workSheet.Cells[1, "A"] = "Make";
workSheet.Cells[1, "B"] = "Color";
workSheet.Cells[1, "C"] = "Pet Name";
// Now, map all data in List<Car> to the cells of the spreadsheet.
int row = 1;
foreach (Car c in carsInStock)
{
row++;
workSheet.Cells[row, "A"] = c.Make;
workSheet.Cells[row, "B"] = c.Color;
workSheet.Cells[row, "C"] = c.PetName;
}
// Give our table data a nice look and feel.
workSheet.Range["A1"].AutoFormat(XlRangeAutoFormat.xlRangeAutoFormatClassic2);
// Save the file, quit Excel, and display message to user.
workSheet.SaveAs($@"{Environment.CurrentDirectory}\Inventory.xlsx");
excelApp.Quit();
Console.WriteLine("The Inventory.xlsx file has been saved to your app folder");
}
/// <summary>
/// The FL_DataTableToExcel
/// </summary>
/// <param name="DataTable">The DataTable<see cref="DataTable"/></param>
/// <param name="ExcelFilePath">The ExcelFilePath<see cref="string"/></param>
public static void FL_DataTableToExcel(DataTable DataTable, string ExcelFilePath)
{
try
{
var unused = (DataTable.Columns.Count + 1) * (DataTable.Rows.Count + 1);
int columnsCount;
var v = (columnsCount = DataTable.Columns.Count);
if (v == 0)
{
//MessageBox.Show("FL_Excel_Data_Interop.FL_DataTableToExcel_Helper.FL_DataTableToExcel: Null or empty input table!", "Error..!!", MessageBoxButton.OK, MessageBoxImage.Error);
MessageBox.Show("Null or empty input table!", "Error..!!", MessageBoxButton.OK, MessageBoxImage.Error);
}
// load excel, and create a new workbook
var excel = new Application();
excel.Workbooks.Add();
//Excel.Visible = true;
// single worksheet
_Worksheet worksheet = excel.ActiveSheet;
var header = new object[columnsCount];
//var stopwatch = new Stopwatch();
//stopwatch.Reset();
//stopwatch.Start();
// column headings
for (var i = 0; i < columnsCount; i++)
{
header[i] = DataTable.Columns[i].ColumnName;
}
var headerRange = worksheet.Range[(Range)(worksheet.Cells[1, 1]), (Range)(worksheet.Cells[1, columnsCount])];
headerRange.Value = header;
headerRange.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
//HeaderRange.Interior.Color = SystemColors.GrayTextBrush;
headerRange.Font.Bold = true;
// DataCells
var rowsCount = DataTable.Rows.Count;
var cells = new object[rowsCount, columnsCount];
for (var j = 0; j < rowsCount; j++)
{
for (var i = 0; i < columnsCount; i++)
{
cells[j, i] = DataTable.Rows[j][i];
}
//rel_cells = 0;
//rel_cells = ColumnsCount * j;
//var time = stopwatch.ElapsedMilliseConds;
//var speed = (rel_cells / time);
//Console.WriteLine("Speed:" + speed + "cells/sec");
}
worksheet.Range[(Range)(worksheet.Cells[2, 1]), (Range)(worksheet.Cells[rowsCount + 1, columnsCount])].Value2 = cells;
//stopwatch.Stop();
//var final_speed = (total_cells / stopwatch.ElapsedMilliseConds);
//Console.WriteLine("Completed At Speed:" + final_speed + "cells/sec");
if (string.IsNullOrEmpty(ExcelFilePath) || File.Exists(ExcelFilePath))
{
excel.Visible = true;
}
else
{ // no file path is given
try
{
worksheet.SaveAs(ExcelFilePath);
excel.Quit();
//MessageBox.Show("Excel file saved as "+ExcelFilePath,"DataTable Saved In Excel File",MessageBoxButton.OK,MessageBoxImage.Information);
}
catch (Exception ex)
{
MessageBox.Show("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message, "Error..!!", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
Marshal.FinalReleaseComObject(worksheet);
Marshal.FinalReleaseComObject(headerRange);
Marshal.FinalReleaseComObject(excel);
//System.Windows.MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error..!!", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void btnExportOpen_Click(object sender, EventArgs e)
{
//creates excel obj
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
//creates a work book
_Workbook workbook = excel.Workbooks.Add(Type.Missing);
//make a worksheet and sets it to null
_Worksheet worksheet = null;
try
{
//set active sheet
worksheet = workbook.ActiveSheet;
worksheet.Name = "Business Contacts";
// b/c both data grids and excel work sheets are tabular, we must use nexted loops to write from one to another
//controls the row number
for (int rowIndex = 0; rowIndex < dataGridView1.Rows.Count - 1; rowIndex++)
{
//used to go over columns of each row
for (int colIndex = 0; colIndex < dataGridView1.Columns.Count; colIndex++)
{
if (rowIndex == 0)
{
//in excel rows row and column start w/ 1,1 not 0,0
//write out header text from grid view to excel sheet
worksheet.Cells[rowIndex + 1, colIndex + 1] = dataGridView1.Columns[colIndex].HeaderText;
}
else
{
//fix row idex at 1 then change column index over possible values from 0 to 5
worksheet.Cells[rowIndex + 1, colIndex + 1] = dataGridView1.Rows[rowIndex].Cells[colIndex].Value.ToString();
}
}
}
//user clicks okay to save
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
//save file to drive
worksheet.SaveAs(saveFileDialog1.FileName);
Process.Start("excel.exe", saveFileDialog1.FileName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//close excel
excel.Quit();
//empty work book
workbook = null;
excel = null;
}
}
/// <summary>
/// Export DataTable to Excel file
/// </summary>
/// <param name="dataTable">Source DataTable</param>
/// <param name="excelFilePath">Path to result file name</param>
public static void ExportToExcel(this DataTable dataTable, string excelFilePath = null)
{
try
{
int columnsCount;
if (dataTable == null || (columnsCount = dataTable.Columns.Count) == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
Application Excel = new Application();
Excel.Workbooks.Add();
// single worksheet
_Worksheet worksheet = Excel.ActiveSheet;
object[] header = new object[columnsCount];
// column headings
for (var i = 0; i < columnsCount; i++)
{
header[i] = dataTable.Columns[i].ColumnName;
}
var headerRange = worksheet.Range[(Range)(worksheet.Cells[1, 1]), (Range)(worksheet.Cells[1, columnsCount])];
headerRange.Value = header;
headerRange.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
headerRange.Font.Bold = true;
// DataCells
var rowsCount = dataTable.Rows.Count;
object[,] cells = new object[rowsCount, columnsCount];
for (var j = 0; j < rowsCount; j++)
{
for (var i = 0; i < columnsCount; i++)
{
cells[j, i] = dataTable.Rows[j][i];
}
}
worksheet.Range[(Range)(worksheet.Cells[2, 1]), (Range)(worksheet.Cells[rowsCount + 1, columnsCount])].Value = cells;
// check fielpath
if (!string.IsNullOrEmpty(excelFilePath))
{
try
{
worksheet.SaveAs(excelFilePath);
Excel.Quit();
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
Excel.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public static void ExportToExcel(this System.Data.DataTable dtTestCaseResults, int totalScripts, int passScripts, int failScripts, string startTime, string endTime, int ignoredScripts, int executedScriptsCount)
{
string excelFormatName = excelTestResultsPath + "MMDDYYYY_AutomationRunResult.xlsx";
int rowcount = dtTestCaseResults.Rows.Count;
string strFilePath = screenShotParentPath;
string strDateTime = DateTime.Now.ToFileTime().ToString();
Application excelApplication = new Application();
excelApplication.Workbooks.Open(excelFormatName);
_Worksheet excelResultSheet = excelApplication.ActiveSheet;
Range excelCellrange;
excelResultSheet.Name = dtTestCaseResults.TableName;
try
{
if (rowcount > 0)
{
excelResultSheet.Cells[3, 2] = totalScripts.ToString();
excelResultSheet.Cells[4, 2] = executedScriptsCount.ToString();
excelResultSheet.Cells[5, 2] = ignoredScripts.ToString();
excelResultSheet.Cells[6, 2] = passScripts.ToString();
excelResultSheet.Cells[7, 2] = failScripts.ToString();
excelResultSheet.Cells[8, 2] = startTime;
excelResultSheet.Cells[9, 2] = endTime;
for (int j = 0; j < dtTestCaseResults.Rows.Count; j++)
{
for (int k = 0; k < dtTestCaseResults.Columns.Count; k++)
{
excelResultSheet.Cells[j + 15, k + 1] = dtTestCaseResults.Rows[j].ItemArray[k].ToString().Trim();
}
}
excelCellrange = excelResultSheet.Range[excelResultSheet.Cells[1, 1], excelResultSheet.Cells[dtTestCaseResults.Rows.Count + 1, dtTestCaseResults.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Range formatHeader = excelResultSheet.Range[excelResultSheet.Cells[1, 1], excelResultSheet.Cells[1, dtTestCaseResults.Columns.Count]];
formatHeader.EntireRow.Font.Bold = true;
formatHeader.EntireRow.Font.Color = Color.White;
formatHeader.Interior.Color = Color.SteelBlue;
Range formatMessageCells = excelResultSheet.Range[excelResultSheet.Cells[1, 8], excelResultSheet.Cells[dtTestCaseResults.Rows.Count + 1, 9]];
formatMessageCells.RowHeight = 15;
formatMessageCells.EntireColumn.AutoFit();
formatMessageCells.ColumnWidth = 70;
}
else
{
excelResultSheet.Cells[1, 1] = "No results found!";
}
}
catch
{
}
finally
{
excelResultSheet.SaveAs(Path.Combine(strFilePath, "\\TestResult " + screenShotParentPath.Split('/')[1] + ".xlsx"));
excelApplication.Quit();
}
}
// Export DataTable into an excel file with field names in the header line
// - Save excel file without ever making it visible if filepath is given
// - Don't save excel file, just make it visible if no filepath is given
public static string ExportToExcel
(
DataTable tbl,
string excelFilePath = null)
{
if (string.IsNullOrEmpty(excelFilePath))
{
return(excelFilePath);
}
excelFilePath = excelFilePath + "LogBook_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
try
{
if (tbl == null || tbl.Columns.Count == 0)
{
MessageDialog.Show
(
"ExportToExcel: Null or empty input table!",
"Error Log Not found For This Date!"
);
return(null);
}
// load excel, and create a new workbook
var excelApp = new Application();
excelApp.Workbooks.Add();
// single worksheet
_Worksheet workSheet = excelApp.ActiveSheet;
// column headings
for (int i = 0;
i < tbl.Columns.Count;
i++)
{
workSheet.Cells[1,
(i + 1)] = tbl.Columns[i].ColumnName;
}
// rows
for (int i = 0;
i < tbl.Rows.Count;
i++)
{
// to do: format datetime values before printing
for (int j = 1;
j < tbl.Columns.Count;
j++)
{
workSheet.Cells[(i + 2),
(j + 1)] = tbl.Rows[i][j];
}
}
try
{
workSheet.SaveAs(excelFilePath);
excelApp.Quit();
return(excelFilePath);
// MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception
(
"ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
static void Main(string[] args)
{
Application xlApp = new Application();
Workbook xlWorkBook = xlApp.Workbooks
.Open(@"C:\FF\211439_1_19_2020_copy.xlsx");
// Read Defensive Data
AllDefensiveRecords.ReadExcel(xlWorkBook);
// Read Kicker Data
KickerRecord.ReadExcel(xlWorkBook);
// Read Offensive Data
AllOffensiveRecords.ReadExcel(xlWorkBook);
// Read Schedule
ScheduleRecord.ReadExcel(xlWorkBook);
// Read Team Players
TeamPlayersRecord.ReadExcel(xlWorkBook);
// Read Teams
Team.ReadExcel(xlWorkBook);
xlWorkBook.Close();
// For each team summarize offensive record
// For each tema summarize defensive record
foreach (Team t in Team.Teams)
{
t.SummarizeRecord(true);
t.SummarizeRecord(false);
}
// Find average def values
foreach (Team t in Team.Teams)
{
t.DefenseSummary();
}
// Find average offensive coef
foreach (Team t in Team.Teams)
{
t.OffenseSummary();
}
// Calculate team counts
foreach (Team t in Team.Teams)
{
t.PosCounts();
}
// For each team find pos / metric coeficients
foreach (Team t in Team.Teams)
{
t.CalculatePosCoef();
}
// For each player summarize coeficients
Player.Init();
foreach (Player p in Player.AllPlayers)
{
p.FindPrevCoef();
}
// For each team find team players expected coeficients
foreach (Team t in Team.Teams)
{
t.UpdatePlayerCoef();
}
// For each team update schedule
foreach (Team t in Team.Teams)
{
t.UpdateSchedule();
}
// For each player find expected points
foreach (Player p in Player.AllPlayers)
{
p.FindExpectedPoints();
}
xlApp = new Application();
xlApp.Workbooks.Add();
_Worksheet workSheet = xlApp.ActiveSheet;
workSheet.Name = "Coeficients";
int col = 1;
workSheet.Cells[1, col++] = "Name";
workSheet.Cells[1, col++] = "Position";
foreach (string m in Enum.GetNames(typeof(Metric)))
{
workSheet.Cells[1, col++] = m;
}
col = 1;
int row = 2;
foreach (Player p in Player.AllPlayers)
{
workSheet.Cells[row, col++] = p.Name;
workSheet.Cells[row, col++] = p.Position.ToString();
foreach (Metric m in Enum.GetValues(typeof(Metric)))
{
workSheet.Cells[row, col++] = p.realCoef[m];
}
row++;
col = 1;
}
xlApp.Worksheets.Add();
workSheet = xlApp.ActiveSheet;
workSheet.Name = "Weeks";
col = 1;
workSheet.Cells[1, col++] = "Name";
workSheet.Cells[1, col++] = "Position";
workSheet.Cells[1, col++] = "Team";
workSheet.Cells[1, col++] = "Drafted";
workSheet.Cells[1, col++] = "ADP";
workSheet.Cells[1, col++] = "Tier";
for (int i = 1; i < 17; i++)
{
workSheet.Cells[1, col++] = "Week " + i;
}
col = 1;
row = 2;
foreach (Player p in Player.AllPlayers)
{
workSheet.Cells[row, col++] = p.Name;
workSheet.Cells[row, col++] = p.Position.ToString();
workSheet.Cells[row, col++] = p.Team.ShortName;
col += 3;
for (int i = 1; i < 17; i++)
{
if (p.expectedPoints.ContainsKey(i))
{
workSheet.Cells[row, col++] = p.expectedPoints[i];
}
else
{
workSheet.Cells[row, col++] = 0;
}
}
row++;
col = 1;
}
workSheet.SaveAs(@"C:\FF\testNew.xlsx");
xlApp.Quit();
}
/// <summary>
/// Export DataTable to Excel file
/// </summary>
/// <param name="dataTable">Source DataTable</param>
/// <param name="excelFilePath">Path to result file name</param>
public static void ExportToExcel(this DataTable dataTable, bool colorize = false, string excelFilePath = null)
{
try
{
int columnsCount;
if (dataTable == null || (columnsCount = dataTable.Columns.Count) == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
Application excel = new Application();
excel.Workbooks.Add();
// single worksheet
_Worksheet worksheet = (_Worksheet)excel.ActiveSheet;
object[] header = new object[columnsCount];
// column headings
for (int i = 0; i < columnsCount; i++)
{
header[i] = dataTable.Columns[i].ColumnName;
}
Range headerRange = worksheet.Range[(Range)(worksheet.Cells[1, 1]), (Range)(worksheet.Cells[1, columnsCount])];
headerRange.Value = header;
headerRange.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
headerRange.Font.Bold = true;
// DataCells
int rowsCount = dataTable.Rows.Count;
object[,] cells = new object[rowsCount, columnsCount];
for (int j = 0; j < rowsCount; j++)
{
for (int i = 0; i < columnsCount; i++)
{
cells[j, i] = dataTable.Rows[j][i];
}
}
worksheet.Range[(Range)(worksheet.Cells[2, 1]), (Range)(worksheet.Cells[rowsCount + 1, columnsCount])].Value = cells;
((Range)worksheet.Columns[2]).Insert();
((Range)worksheet.Cells[1, 2]).Value = "Dossier";
string memHash = colorize? dataTable.Rows[1]["Hash"].ToString():null;
Color[] colTab = new Color[] { Color.FromArgb(202, 244, 181), Color.FromArgb(255, 212, 157) };
int colorInUse = 0;
for (int j = 0; j < rowsCount; j++)
{
worksheet.Hyperlinks.Add(worksheet.Cells[j + 2, 2], "file://" + Path.GetDirectoryName(((Range)worksheet.Cells[j + 2, 1]).Value.ToString()), Type.Missing, "Ouvrir le dossier", "Ouvrir le dossier");
worksheet.Hyperlinks.Add(worksheet.Cells[j + 2, 1], "file://" + ((Range)worksheet.Cells[j + 2, 1]).Value, Type.Missing, ((Range)worksheet.Cells[j + 2, 1]).Value, ((Range)worksheet.Cells[j + 2, 1]).Value);
if (colorize && memHash != dataTable.Rows[j]["Hash"].ToString())
{
colorInUse = colorInUse == 0 ? 1 : 0;
memHash = dataTable.Rows[j]["Hash"].ToString();
}
worksheet.Range[(Range)(worksheet.Cells[j + 2, 1]), (Range)(worksheet.Cells[j + 2, columnsCount])].Interior.Color = ColorTranslator.ToOle(colTab[colorInUse]);
}
worksheet.Range[(Range)(worksheet.Cells[2, 1]), (Range)(worksheet.Cells[rowsCount + 1, columnsCount])].Columns.AutoFit();
// check fielpath
if (!string.IsNullOrEmpty(excelFilePath))
{
try
{
worksheet.SaveAs(excelFilePath);
excel.Quit();
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excel.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
