internal void ExelControll()
{
excelApp.Visible = true;
excelApp.Workbooks.Add();
excelApp.WindowState = Excel.XlWindowState.xlMinimized;
mySheet = (Excel.Worksheet)excelApp.ActiveSheet;
mySheet.Cells[1, "A"] = "Lfd. Nr. ";
mySheet.Cells[1, "B"] = "Profl Bezeichnung ";
mySheet.Cells[1, "C"] = "Höhe in mm ";
mySheet.Cells[1, "D"] = "Breite in mm ";
mySheet.Cells[1, "E"] = "Durchmesser in mm ";
mySheet.Cells[1, "F"] = "Wandstärke t in mm ";
mySheet.Cells[1, "G"] = "Wandstärke s in mm ";
mySheet.Cells[1, "H"] = "Länge in mm ";
mySheet.Cells[1, "I"] = "Fläche As in mm² ";
mySheet.Cells[1, "J"] = "Volumen in mm³ ";
mySheet.Cells[1, "K"] = "Wy in cm³ ";
mySheet.Cells[1, "L"] = "Wz in cm³ ";
mySheet.Cells[1, "M"] = "Iy in cm^4 ";
mySheet.Cells[1, "N"] = "Iz in cm^4 ";
mySheet.Cells[1, "O"] = "FTM Polar in cm^4 ";
last = mySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Xrange = mySheet.get_Range("A1", last);
myBorders = Xrange.Borders;
myBorders.LineStyle = Excel.XlLineStyle.xlContinuous;
myBorders.Weight = 4d;
//Einstellung der Schriftgröße
mySheet.Range["A1"].EntireRow.Font.Size = 14;
mySheet.Range["B2"].EntireColumn.Font.Size = 12;
mySheet.Range["C2"].EntireColumn.Font.Size = 12;
mySheet.Range["D2"].EntireColumn.Font.Size = 12;
mySheet.Range["E2"].EntireColumn.Font.Size = 12;
mySheet.Range["F2"].EntireColumn.Font.Size = 12;
mySheet.Range["G2"].EntireColumn.Font.Size = 12;
mySheet.Range["H2"].EntireColumn.Font.Size = 12;
mySheet.Range["I2"].EntireColumn.Font.Size = 12;
mySheet.Range["J2"].EntireColumn.Font.Size = 12;
mySheet.Range["K2"].EntireColumn.Font.Size = 12;
mySheet.Range["L2"].EntireColumn.Font.Size = 12;
mySheet.Range["M2"].EntireColumn.Font.Size = 12;
mySheet.Range["N2"].EntireColumn.Font.Size = 12;
//Geschriebe Daten in Kusiv
mySheet.Range["B2", "Q2"].EntireColumn.Font.Italic = true;
//Eingaben Zentrieren
mySheet.Range["A1", "Q1"].EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//Schriftzug Fettwerden lassen
mySheet.Range["A1"].EntireRow.Font.Bold = true;
mySheet.Range["B2"].EntireColumn.Font.Bold = true;
mySheet.Range["A1", "Q2"].EntireColumn.AutoFit();
}
void createExcell()
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook worKbooK;
Microsoft.Office.Interop.Excel.Worksheet worKsheeT;
Microsoft.Office.Interop.Excel.Range celLrangE;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = openFileDialog.SafeFileName.ToString();
int rowcount = 1;
foreach (DataRow datarow in ExportToExcel().Rows)
{
rowcount += 1;
for (int i = 1; i <= ExportToExcel().Columns.Count; i++)
{
if (rowcount == 3)
{
worKsheeT.Cells[1, i] = ExportToExcel().Columns[i - 1].ColumnName;
worKsheeT.Cells.Font.Color = System.Drawing.Color.Black;
}
worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3)
{
if (i == ExportToExcel().Columns.Count)
{
if (rowcount % 2 == 0)
{
celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];
}
}
}
}
}
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];
celLrangE.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, ExportToExcel().Columns.Count]];
worKbooK.SaveAs(exportName.Text.ToString() + po_value);
for (int i = 0; i <= 10; i++)
{
CopyWithProgress(totLine);
}
worKbooK.Close();
excel.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
worKsheeT = null;
celLrangE = null;
worKbooK = null;
}
}
private void CreatePrintFiles(SendAlbum album, String NameFile, int Format)
{
int line = 1;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
Workbook worKbooK = excel.Workbooks.Add(Type.Missing);
Worksheet worKsheeT = (Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "SoapOperaPrint";
try {
line = 1;
worKsheeT.Cells[line, 1] = album.Name;
line = 2;
worKsheeT.Cells[line, 1] = "№";
worKsheeT.Cells[line, 2] = "Участник";
if (album.isUseSize)
{
for (int i = 3; i < album.Photos.Count * 2 + 3; i = i + 2)
{
worKsheeT.Cells[line, i] = album.Photos[(i - 3) / 2];
worKsheeT.Cells[line, i].Orientation = 90;
}
}
else
{
for (int i = 3; i < album.Photos.Count + 3; i++)
{
worKsheeT.Cells[line, i] = album.Photos[i - 3];
worKsheeT.Cells[line, i].Orientation = 90;
}
}
line = 3;
for (int i = 0; i < album.Peoples.Count; i++)
{
worKsheeT.Cells[line, 1] = (i + 1);
worKsheeT.Cells[line, 2] = album.Peoples[i].Name;
for (int j = 0; j < album.Peoples[i].Product.Count; j++)
{
for (int k = 0; k < album.Photos.Count; k++)
{
if (album.Peoples[i].Product[j].Name == album.Photos[k])
{
if (album.isUseSize)
{
worKsheeT.Cells[line, 3 + k * 2] = album.Peoples[i].Product[j].Size;
worKsheeT.Cells[line, 3 + k * 2 + 1] = album.Peoples[i].Product[j].Count;
}
else
{
worKsheeT.Cells[line, 3 + k] = album.Peoples[i].Product[j].Count;
}
}
}
}
line++;
}
int countCollum = 0;
if (album.isUseSize)
{
countCollum = 2 + album.Photos.Count * 2;
}
else
{
countCollum = 2 + album.Photos.Count;
}
Microsoft.Office.Interop.Excel.Borders border = worKsheeT.Range[worKsheeT.Cells[2, 1], worKsheeT.Cells[line - 1, countCollum]].Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
Microsoft.Office.Interop.Excel.Range usedrange = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[line - 1, countCollum]];
usedrange.Rows.AutoFit();
usedrange.Columns.AutoFit();
try {
if (Format <= 0)
{
worKbooK.SaveAs(NameFile, XlFileFormat.xlExcel3);
}
else
{
worKbooK.SaveAs(NameFile);
}
}
catch (Exception ex2) {
throw new BadFormatSafeException();
}
worKbooK.Close();
}
catch (Exception ex) {
throw ex;
}
finally {
IntPtr handle = (IntPtr)excel.Hwnd;
excel.Quit();
MyFunctions.TerminateProcess(handle, 1);
}
}
public void excel()
{
string data;
try
{
DataTable floorArray = dalGet.GetFloorTable();
int floorCount = 0;
foreach (DataRow row in floorArray.Rows)
{
floorCount++;
}
Microsoft.Office.Interop.Excel.Application excApp = new Excel.Application();
// книга
excApp.Visible = true;
excApp.SheetsInNewWorkbook = floorCount++;
excApp.Workbooks.Add(Type.Missing);
// настройка стиля ссылок
excApp.ReferenceStyle = Excel.XlReferenceStyle.xlA1;
// временно неактивный докуметн
excApp.Interactive = false;
excApp.EnableEvents = false;
Microsoft.Office.Interop.Excel.XlBordersIndex BorderIndex;
Excel.Worksheet excSheet;
double height = 2;
//мутим файл
// for (int i = 0; i < floorArray.Rows.Count-1; i++)
// excSheet = (Excel.Worksheet)excApp.Worksheets.Add();
for (int i = 0; i < floorArray.Rows.Count; i++)
{
int coll = 1;
int row = 1;
// excSheet = (Excel.Worksheet)excApp.Worksheets.Add();
excSheet = (Excel.Worksheet)excApp.Worksheets.get_Item(1 + i);
excSheet.Name = floorArray.Rows[i].ItemArray[0].ToString() + " этаж";
DataTable roomArray = dalGet.GetRoomTabel(floorArray.Rows[i].ItemArray[0].ToString());
for (int t = 0; t < roomArray.Rows.Count; t++)
{
int frameRoomColl = coll;
int framRoomRow = row;
excSheet.Activate();
Excel.Range excSheetRange = excSheet.get_Range("A" + row, "I" + row);
excSheetRange.Font.Size = 5;
excSheetRange.RowHeight = height;
excSheetRange.Rows.ColumnWidth =
excSheetRange.Interior.ColorIndex = 1;
row++;
excSheetRange.Columns[1].ColumnWidth = 5;
excSheetRange.Columns[2].ColumnWidth = 5;
excSheetRange.Columns[3].ColumnWidth = 19;
excSheetRange.Columns[4].ColumnWidth = 15;
excSheetRange.Columns[5].ColumnWidth = 10;
excSheetRange.Columns[6].ColumnWidth = 18;
excSheetRange.Columns[7].ColumnWidth = 25;
excSheetRange.Columns[8].ColumnWidth = 25;
excSheetRange.Columns[9].ColumnWidth = 37;
excSheetRange = excSheet.get_Range("A" + row, "I" + row);
excSheetRange.Merge(Type.Missing);
excSheetRange.WrapText = true;
excSheetRange.Font.Bold = true;
excSheetRange.Font.Size = 18;
excSheetRange.Interior.ColorIndex = 37;
excSheetRange.HorizontalAlignment = Excel.Constants.xlCenter;
excSheetRange.VerticalAlignment = Excel.Constants.xlCenter;
excSheetRange.Value2 = "Комната: " + roomArray.Rows[t].ItemArray[0].ToString() +
", этаж: " + floorArray.Rows[i].ItemArray[0].ToString();
row++;
excSheetRange = excSheet.get_Range("B" + row, "I" + row);
excSheetRange.WrapText = true;
excSheetRange.Font.Italic = true;
excSheetRange.Font.Size = 11;
excSheetRange.Interior.ColorIndex = 40;
excSheetRange.HorizontalAlignment = Excel.Constants.xlCenter;
excSheetRange.VerticalAlignment = Excel.Constants.xlCenter;
Excel.Borders border = excSheetRange.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
excSheet.Cells[row, 3] = "дата записи в БД";
excSheet.Cells[row, 4] = "Инвентарный номер";
excSheet.Cells[row, 5] = "Тип учёта";
excSheet.Cells[row, 6] = "Ответственный";
excSheet.Cells[row, 7] = "Тип устройства";
excSheet.Cells[row, 8] = "Серийный номер";
excSheet.Cells[row, 9] = "Модель";
row++;
DataTable LanNameArray = dalGet.GetLanNameTabel(roomArray.Rows[t].ItemArray[0].ToString());
for (int w = 0; w < LanNameArray.Rows.Count; w++)
{
excSheetRange = excSheet.get_Range("B" + row, "I" + row);
excSheetRange.Merge(Type.Missing);
excSheetRange.Interior.ColorIndex = 1;
excSheetRange.Font.Size = 2;
excSheetRange.RowHeight = height;
row++;
excSheetRange = excSheet.get_Range("B" + row, "I" + row);
excSheetRange.Merge(Type.Missing);
excSheetRange.WrapText = true;
excSheetRange.Font.Bold = true;
excSheetRange.Font.Size = 14;
excSheetRange.Interior.ColorIndex = 39;
excSheetRange.HorizontalAlignment = Excel.Constants.xlCenter;
excSheetRange.VerticalAlignment = Excel.Constants.xlCenter;
excSheetRange.Value2 = LanNameArray.Rows[w].ItemArray[0].ToString() + ":";
row++;
DataTable MainArray = dalGet.GetMainTabel(LanNameArray.Rows[w].ItemArray[0].ToString(),
roomArray.Rows[t].ItemArray[0].ToString());
for (int x = 0; x < MainArray.Rows.Count; x++)
{
// excSheetRange.Font.Size = 12;
//border = excSheetRange.Borders;
//border.LineStyle = Excel.XlLineStyle.xlContinuous;
excSheetRange = excSheet.get_Range("d" + row, "I" + row);
excSheetRange.NumberFormat = "@";
for (int z = 0; z < MainArray.Columns.Count; z++)
{
data = MainArray.Rows[x].ItemArray[z].ToString();
excSheet.Cells[row, z + 3] = data;
}
excSheetRange = excSheet.get_Range("C" + row);
excSheetRange.NumberFormat = "dd.mmmm.yyyy";
excSheetRange = excSheet.get_Range("C" + row, "I" + row);
excSheetRange.Font.Size = 12;
border = excSheetRange.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
row++;
//excSheetRange.Columns.AutoFit();
excSheetRange.Rows.AutoFit();
}
excSheetRange = excSheet.get_Range("B" + (framRoomRow + 3), "I" + (row - 1));
BorderIndex = Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft;
excSheetRange.Borders[BorderIndex].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excSheetRange.Borders[BorderIndex].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excSheetRange.Borders[BorderIndex].ColorIndex = 0;
BorderIndex = Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop;
excSheetRange.Borders[BorderIndex].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excSheetRange.Borders[BorderIndex].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excSheetRange.Borders[BorderIndex].ColorIndex = 0;
BorderIndex = Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom;
excSheetRange.Borders[BorderIndex].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excSheetRange.Borders[BorderIndex].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excSheetRange.Borders[BorderIndex].ColorIndex = 0;
BorderIndex = Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight;
excSheetRange.Borders[BorderIndex].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excSheetRange.Borders[BorderIndex].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excSheetRange.Borders[BorderIndex].ColorIndex = 0;
}
excSheetRange = excSheet.get_Range("A" + framRoomRow, "I" + (row - 1));
BorderIndex = Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft;
excSheetRange.Borders[BorderIndex].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excSheetRange.Borders[BorderIndex].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excSheetRange.Borders[BorderIndex].ColorIndex = 0;
BorderIndex = Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop;
excSheetRange.Borders[BorderIndex].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excSheetRange.Borders[BorderIndex].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excSheetRange.Borders[BorderIndex].ColorIndex = 0;
BorderIndex = Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom;
excSheetRange.Borders[BorderIndex].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excSheetRange.Borders[BorderIndex].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excSheetRange.Borders[BorderIndex].ColorIndex = 0;
BorderIndex = Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight;
excSheetRange.Borders[BorderIndex].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
excSheetRange.Borders[BorderIndex].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
excSheetRange.Borders[BorderIndex].ColorIndex = 0;
//excSheetRange = excSheet.UsedRange;
releaseObject(excSheetRange);
}
releaseObject(excSheet);
}
// excApp.Visible = true;
excApp.Interactive = true;
excApp.ScreenUpdating = true;
excApp.UserControl = true;
releaseObject(excApp);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook worKbooK;
Microsoft.Office.Interop.Excel.Worksheet worKsheeT;
Microsoft.Office.Interop.Excel.Range celLrangE;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "StudentRepoertCard";
worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 8]].Merge();
worKsheeT.Cells[1, 1] = "Student Report Card";
worKsheeT.Cells.Font.Size = 15;
int rowcount = 2;
foreach (DataRow datarow in ExportToExcel().Rows)
{
rowcount += 1;
for (int i = 1; i <= ExportToExcel().Columns.Count; i++)
{
if (rowcount == 3)
{
worKsheeT.Cells[2, i] = ExportToExcel().Columns[i - 1].ColumnName;
worKsheeT.Cells.Font.Color = System.Drawing.Color.Black;
}
worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3)
{
if (i == ExportToExcel().Columns.Count)
{
if (rowcount % 2 == 0)
{
celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];
}
}
}
}
}
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];
celLrangE.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, ExportToExcel().Columns.Count]];
worKbooK.SaveAs(textBox1.Text);;
worKbooK.Close();
excel.Quit();
MessageBox.Show("Successfully Create Excel File");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
worKsheeT = null;
celLrangE = null;
worKbooK = null;
}
}
/// <summary>
/// 设置单元格范围
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
public void SetRange(int row, int col)
{
this._range = this._worksheet.Cells[row, col];
this._font = this._range.Font;
this._borders = this._range.Borders;
this._leftBorder = this._borders[Excel.XlBordersIndex.xlEdgeLeft];
this._topBorder = this._borders[Excel.XlBordersIndex.xlEdgeTop];
this._rightBorder = this._borders[Excel.XlBordersIndex.xlEdgeRight];
this._bottomBorder = this._borders[Excel.XlBordersIndex.xlEdgeBottom];
}
public MemoryStream ExportEmployees(List <Employee> employees, string fileName)
{
var dataTable = GetTable(employees);
var folderPath = @"C:\MediaFiles";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
var excel = new Excel.Application();
var excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
var excelSheet = (Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "Eksport pracowników";
//column headers
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
excelSheet.Cells[1, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = Color.Black;
}
int rowcount = 1;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
}
}
// now we resize the columns
var excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
string excelFileName = fileName + ".xlsx";
var newFullFileName = Path.Combine(folderPath, excelFileName);
excelworkBook.SaveAs(newFullFileName);
excelworkBook.Close();
excel.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(excelworkBook);
Marshal.ReleaseComObject(excelCellrange);
Marshal.ReleaseComObject(excel);
MemoryStream ms = new MemoryStream();
using (FileStream fs = File.OpenRead(newFullFileName))
{
fs.CopyTo(ms);
}
ms.Seek(0, SeekOrigin.Begin);
File.Delete(newFullFileName);
return(ms);
}
static public void WriteExcelFileWithCollum(List <List <People> > peoples, List <string> NameAlbum, bool ExeptEmptyPhoto, List <List <string> > AllNamePhoto, int Format, String NameFile)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
Workbook worKbooK = excel.Workbooks.Add(Type.Missing);
Worksheet worKsheeT = (Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "SoapOpera";
int line = 1;
try {
for (int z = 0; z < peoples.Count; z++)
{
if (peoples.Count > 1 && peoples[z].Count == 0)
{
continue;
}
if (peoples.Count == 1 && peoples[z].Count == 0)
{
throw new NotCommentException();
}
bool[] buffPhoto = new bool[peoples[z][0].CommentsText.Count];
int curentLenght = 0;
if (ExeptEmptyPhoto)
{
for (int j = 0; j < peoples[z].Count; j++)
{
for (int k = 0; k < peoples[z][0].CommentsText.Count; k++)
{
if (peoples[z][j].CommentsText[k].Count > 0)
{
buffPhoto[k] = true;
}
}
}
}
else
{
for (int k = 0; k < peoples[z][0].CommentsText.Count; k++)
{
buffPhoto[k] = true;
}
}
for (int j = 0; j < peoples[z][0].CommentsText.Count; j++)
{
if (buffPhoto[j])
{
curentLenght++;
}
}
worKsheeT.Range[worKsheeT.Cells[line, 1], worKsheeT.Cells[line + 2, 5 + curentLenght * 4]].NumberFormat = "@";
worKsheeT.Cells[line, 1] = NameAlbum[z];
line++;
worKsheeT.Cells[line, 1] = "№";
worKsheeT.Cells[line, 2] = "Участник";
worKsheeT.Cells[line, 3] = "id";
int curcounter = 0;
for (int i = 0; i < peoples[z][0].CommentsText.Count; i++)
{
if (buffPhoto[i])
{
worKsheeT.Cells[line, 4 + curcounter * 4] = AllNamePhoto[z][i].Replace('\n', ' ');
curcounter++;
}
}
worKsheeT.Cells[line, 4 + curentLenght * 4] = "Сумма к оплате";
worKsheeT.Cells[line, 5 + curentLenght * 4] = "Предоставлено к оплате";
line++;
for (int i = 4; i < 3 + curentLenght * 4; i = i + 4)
{
worKsheeT.Cells[line, i] = "Комментарии";
worKsheeT.Cells[line, i + 1] = "Размерность";
worKsheeT.Cells[line, i + 2] = "Кол-во";
worKsheeT.Cells[line, i + 3] = "Цена";
}
line++;
int lineStartPeople = line;
for (int i = 0; i < peoples[z].Count; i++)
{
int offset = 0;
string[] buff = new string[peoples[z][i].CommentsText.Count];
for (int j = 0; j < peoples[z][i].CommentsText.Count; j++)
{
buff[j] = "";
}
while (true)
{
worKsheeT.Range[worKsheeT.Cells[line, 1], worKsheeT.Cells[line, 5 + curentLenght * 4]].NumberFormat = "@";
worKsheeT.Cells[line, 1] = (i + 1);
worKsheeT.Cells[line, 2] = peoples[z][i].Name;
worKsheeT.Cells[line, 3] = peoples[z][i].Id;
worKsheeT.Cells[line, 3].Interior.Color = XlRgbColor.rgbGray;
bool prov = false;
curcounter = 4;
for (int j = 4; j < 4 + peoples[z][i].CommentsText.Count * 3; j = j + 3)
{
if (buffPhoto[(j - 4) / 3])
{
worKsheeT.Range[worKsheeT.Cells[line, curcounter + 2], worKsheeT.Cells[line, curcounter + 3]].NumberFormat = "0";
worKsheeT.Cells[line, curcounter + 3].Interior.Color = XlRgbColor.rgbYellow;
if (peoples[z][i].CommentsText[(j - 4) / 3].Count > offset)
{
if (buff[(j - 4) / 3].Length == 0)
{
buff[(j - 4) / 3] = peoples[z][i].CommentsText[(j - 4) / 3][offset];
}
else
{
buff[(j - 4) / 3] = buff[(j - 4) / 3] + " // " + peoples[z][i].CommentsText[(j - 4) / 3][offset];
}
worKsheeT.Cells[line, curcounter] = buff[(j - 4) / 3];
worKsheeT.Cells[line, curcounter + 1] = worKsheeT.Cells[line, curcounter + 2] = worKsheeT.Cells[line, curcounter + 3] = "";
prov = true;
}
curcounter = curcounter + 4;
}
}
if (!prov)
{
worKsheeT.Range[worKsheeT.Cells[line, curcounter], worKsheeT.Cells[line, curcounter + 1]].NumberFormat = "0";
string buff2 = "= " + convertTo(6) + line + " * " + convertTo(7) + line;
for (int j = 1; j < curentLenght; j++)
{
buff2 = buff2 + " + " + convertTo(6 + j * 4) + line + " * " + convertTo(7 + j * 4) + line;
}
worKsheeT.Cells[line, curcounter] = buff2;
line++;
break;
}
else
{
offset++;
}
}
}
worKsheeT.Range[worKsheeT.Cells[line, 1], worKsheeT.Cells[line, 5 + curentLenght * 4]].Interior.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbWhite;
Microsoft.Office.Interop.Excel.Borders border = worKsheeT.Range[worKsheeT.Cells[lineStartPeople, 1], worKsheeT.Cells[line - 1, 5 + curentLenght * 4]].Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
Microsoft.Office.Interop.Excel.Range usedrange = worKsheeT.Range[worKsheeT.Cells[lineStartPeople - 3, 1], worKsheeT.Cells[lineStartPeople - 1, 5 + curentLenght * 4]];
usedrange.Rows.AutoFit();
usedrange.Columns.AutoFit();
usedrange = worKsheeT.Range[worKsheeT.Cells[lineStartPeople - 3, 2], worKsheeT.Cells[line - 1, 2]];
usedrange.Rows.AutoFit();
usedrange.Columns.AutoFit();
line++;
}
if (Format <= 0)
{
worKbooK.SaveAs(NameFile, XlFileFormat.xlExcel3);
}
else
{
worKbooK.SaveAs(NameFile);
}
worKbooK.Close();
}
catch (Exception e) {
throw e;
}
finally {
IntPtr handle = (IntPtr)excel.Hwnd;
excel.Quit();
TerminateProcess(handle, 1);
}
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
//Instantiate the Application object.
Excel.Application ExcelApp = Application;
//Add a Workbook.
Excel.Workbook objBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
//Get the First sheet.
Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Sheets["Sheet1"];
//Put some text into different cells (A2, A4, A6, A8).
objSheet.Cells[2, 1] = "Hair Lines";
objSheet.Cells[4, 1] = "Thin Lines";
objSheet.Cells[6, 1] = "Medium Lines";
objSheet.Cells[8, 1] = "Thick Lines";
//Define a range object(A2).
Excel.Range _range;
_range = objSheet.get_Range("A2", "A2");
//Get the borders collection.
Excel.Borders borders = _range.Borders;
//Set the hair lines style.
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = 1d;
//Define a range object(A4).
_range = objSheet.get_Range("A4", "A4");
//Get the borders collection.
borders = _range.Borders;
//Set the thin lines style.
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = 2d;
//Define a range object(A6).
_range = objSheet.get_Range("A6", "A6");
//Get the borders collection.
borders = _range.Borders;
//Set the medium lines style.
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = 3d;
//Define a range object(A8).
_range = objSheet.get_Range("A8", "A8");
//Get the borders collection.
borders = _range.Borders;
//Set the thick lines style.
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = 4d;
//Auto-fit Column A.
objSheet.get_Range("A2", "A2").EntireColumn.AutoFit();
//Save the excel file.
objBook.SaveAs("ApplyBorders.xls",
Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing,
Type.Missing);
//Quit the Application.
ExcelApp.Quit();
}
private void Button1_Click(object sender, EventArgs e)
{
#region Excel
Excel.Application excelApp = new Excel.Application(); // Создаём экземпляр нашего приложения
Excel.Workbook workBook; // Создаём экземпляр рабочий книги Excel
Excel.Worksheet workSheet; // Создаём экземпляр листа Excel
workBook = excelApp.Workbooks.Add();
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
workSheet.Cells.Font.Color = Color.Red;
workSheet.Cells.Font.Name = "BroadWay"; // Изменение шрифта.
workSheet.Cells.Font.Size = 10; // Изменение размера шрифта.
var j2 = 1;
for (int j = 2; j2 <= 4; j++)
{
// workSheet.Cells[j - 1, j2].Font.Color = Color.Green; // Изменение цвета текста.
workSheet.Cells[2, "A"].Value2 = "test";
if (j == 5)
{
j = 1;
j2++;
}
#region Border(рамка)
Excel.Range rng = workSheet.Range[$"A{j}", $"D{j}"]; // Рамка от одного края до другого (то что ты просил =))
Excel.Borders border = rng.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
#endregion
}
/// Открываем созданный excel-файл
excelApp.Visible = true;
excelApp.UserControl = true;
#endregion
#region BD
#region Подключение
string serverName1 = "127.0.0.1"; // Адрес сервера (для локальной базы пишите "localhost")
string userName = "******"; // Имя пользователя
string dbName = "test"; //Имя базы данных
string port = "3306"; // Порт для подключения
string password = ""; // Пароль для подключения
string connStr = "server=" + serverName1 +
";user="******";database=" + dbName +
";port=" + port +
";password="******";";
string sql = "SELECT * FROM t_test"; // Строка запроса
MySqlConnection connection = new MySqlConnection(connStr);
MySqlCommand sqlCom = new MySqlCommand(sql, connection);
connection.Open();
#endregion
sqlCom.ExecuteNonQuery();
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(sqlCom);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
var myData = dt.Select();
for (int i = 0; i < myData.Length; i++)
{
for (int j = 0; j < myData[i].ItemArray.Length; j++)
{
var text = myData[i].ItemArray[j];
//textBox1.Text += text;
workSheet.Cells[$"А{j}"] = "test";
}
}
#endregion
}
private void btnTimKiemInHoaDon_Click(object sender, EventArgs e)
{
if (dtgvTimKiem.Rows.Count > 0) //TH có dữ liệu để ghi
{
//Khai báo và khởi tạo các đối tượng
Excel.Application exApp = new Excel.Application();
Excel.Workbook exBook =
exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1];
Excel.Range range = exSheet.Range["C1:H3"];
Excel.Borders borders = range.Borders;
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = 3d;
Excel.Range range1 = exSheet.Range["A8:M11"];
Excel.Borders borders1 = range.Borders;
borders.LineStyle = Excel.XlLineStyle.xlContinuous;
borders.Weight = 3d;
//Định dạng chung
Excel.Range hoadon = (Excel.Range)exSheet.Cells[1, 3];
exSheet.get_Range("C1:H1").Merge(true);
exSheet.Range["C1", "F1"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
hoadon.Font.Size = 20;
hoadon.Font.Bold = true;
hoadon.Font.Color = Color.FromArgb(219, 82, 13);
hoadon.Value = "CỬA HÀNG FAST FOOD ĐẠT HUY";
Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 5];
exSheet.get_Range("E2:F2").Merge(true);
exSheet.Range["E2", "F2"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
dcCuaHang.Font.Size = 12;
dcCuaHang.Font.Bold = true;
dcCuaHang.Font.Color = Color.Black;
dcCuaHang.Value = "Địa chỉ: Xxx - xXx - XXX - xXX";
Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 5];
exSheet.get_Range("E3:F3").Merge(true);
exSheet.Range["E3", "F3"].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
dtCuaHang.Font.Size = 12;
dtCuaHang.Font.Bold = true;
dtCuaHang.Font.Color = Color.Black;
dtCuaHang.Value = "Điện thoại: xxxxxxxxxx";
Excel.Range header = (Excel.Range)exSheet.Cells[6, 3];
exSheet.get_Range("C6:G6").Merge(true);
header.Font.Size = 20;
header.Font.Bold = true;
header.Font.Color = Color.FromArgb(255, 218, 135);
header.Value = "DANH SÁCH CÁC MẶT HÀNG";
//Định dạng tiêu đề bảng
exSheet.get_Range("A7:G7").Font.Bold = true;
exSheet.get_Range("A7:G7").HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
exSheet.get_Range("A8").Value = "STT";
exSheet.get_Range("B8").Value = "Mã HĐ ";
exSheet.get_Range("B8").ColumnWidth = 20;
exSheet.get_Range("C8").Value = "Mã NV ";
exSheet.get_Range("C8").ColumnWidth = 10;
exSheet.get_Range("D8").Value = "Tên NV ";
exSheet.get_Range("D8").ColumnWidth = 20;
exSheet.get_Range("E8").Value = "Mã KH";
exSheet.get_Range("E8").ColumnWidth = 10;
exSheet.get_Range("F8").Value = "Tên KH";
exSheet.get_Range("F8").ColumnWidth = 20;
exSheet.get_Range("G8").Value = "Mã SP";
exSheet.get_Range("G8").ColumnWidth = 10;
exSheet.get_Range("H8").Value = "Tên SP";
exSheet.get_Range("H8").ColumnWidth = 20;
exSheet.get_Range("I8").Value = "Ngày tạo";
exSheet.get_Range("I8").ColumnWidth = 15;
exSheet.get_Range("J8").Value = "Đơn giá";
exSheet.get_Range("J8").ColumnWidth = 10;
exSheet.get_Range("K8").Value = "Số lượng";
exSheet.get_Range("K8").ColumnWidth = 10;
exSheet.get_Range("M8").Value = "Tổng tiền";
exSheet.get_Range("M8").ColumnWidth = 10;
exSheet.get_Range("L8").Value = "Yêu cầu thêm";
exSheet.get_Range("L8").ColumnWidth = 12;
//In dữ liệu
DataTable dt = dtbase.DataReader("select cthoadonban.idHD, " +
"nhanvien.idNV, nhanvien.hoTenNV," +
"khachhang.idKH, khachhang.hoTenKH, " +
"sanpham.idsp, sanpham.tensp," +
"sanpham.giaTienSP, cthoadonban.soLuong, " +
"cthoadonban.giaTien , " +
"cthoadonban.yeuCau , cthoadonban.ngayTao from cthoadonban " +
"inner join hoadonban on cthoadonban.idhd= hoadonban.idhd " +
"inner join sanpham on cthoadonban.idsp = sanpham.idsp " +
"inner join khachhang on khachhang.idKH = hoadonban.idKH " +
"inner join nhanvien on nhanvien.idNV = hoadonban.idNV " +
"where cthoadonban.idHD = '" + txtTimKiemMaHD.Text + "'");
for (int i = 0; i < dt.Rows.Count; i++)
{
exSheet.get_Range("A" + (i + 9).ToString() + ":M" + (i + 9).ToString()).Font.Bold = false;
exSheet.get_Range("A" + (i + 9).ToString()).Value = (i + 1).ToString();
exSheet.get_Range("B" + (i + 9).ToString()).Value =
dt.Rows[i]["idHD"].ToString();
exSheet.get_Range("C" + (i + 9).ToString()).Value =
dt.Rows[i]["idNV"].ToString();
exSheet.get_Range("D" + (i + 9).ToString()).Value =
dt.Rows[i]["hoTenNV"].ToString();
exSheet.get_Range("E" + (i + 9).ToString()).Value =
dt.Rows[i]["idKH"].ToString();
exSheet.get_Range("F" + (i + 9).ToString()).Value =
dt.Rows[i]["hoTenKH"].ToString();
exSheet.get_Range("G" + (i + 9).ToString()).Value =
dt.Rows[i]["idsp"].ToString();
exSheet.get_Range("H" + (i + 9).ToString()).Value =
dt.Rows[i]["tenSP"].ToString();
exSheet.get_Range("I" + (i + 9).ToString()).Value =
dt.Rows[i]["ngayTao"].ToString();
exSheet.get_Range("J" + (i + 9).ToString()).Value =
dt.Rows[i]["giaTienSP"].ToString();
exSheet.get_Range("K" + (i + 9).ToString()).Value =
dt.Rows[i]["soLuong"].ToString();
exSheet.get_Range("M" + (i + 9).ToString()).Value =
dt.Rows[i]["giaTien"].ToString();
exSheet.get_Range("L" + (i + 9).ToString()).Value =
dt.Rows[i]["yeuCau"].ToString();
}
exSheet.Name = "Hang";
exBook.Activate(); //Kích hoạt file Excel
//Thiết lập các thuộc tính của SaveFileDialog
dlgSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc)| *.doc | All files(*.*) | *.* ";
dlgSave.FilterIndex = 1;
dlgSave.AddExtension = true;
dlgSave.DefaultExt = ".xlsx";
if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
exBook.SaveAs(dlgSave.FileName.ToString()); //Lưu file Excel
}
exApp.Quit(); //Thoát khỏi ứng dụng
this.Close();
}
else
{
MessageBox.Show("Không có danh sách hàng để in");
}
}
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
System.IO.Directory.CreateDirectory(exportTempDirectory);
//Console.WriteLine("In WriteDataTableToExcel");
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = "Status Update";
try
{
// add phases above columns
excelSheet.Cells[1, 1] = "Phases: Concept => Planning => Design => Development => UAT => Deployed => Complete => Hold";
excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[1, 16]].Merge();
// loop through each row and add values to our sheet
int rowcount = 2;
int finalColumn = 1;
foreach (DataRow datarow in dataTable.Rows)
{
int exclColumn = 1;
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, exclColumn] = dataTable.Columns[i - 1].ColumnName;
}
// if (datarow[i - 1].ToString() != "")
excelSheet.Cells[rowcount, exclColumn] = datarow[i - 1].ToString();
exclColumn += 1;
finalColumn = exclColumn - 1;
}
// highlight row if it has a status
if (datarow[13].ToString() != "")
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, 16]];
excelCellrange.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#EDFFFF");
//excelCellrange.EntireRow.Font.Bold = true;
}
if (datarow[4].ToString() == "Green")
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 5], excelSheet.Cells[rowcount, 5]];
FormattingExcelCells(excelCellrange, "#85e085", System.Drawing.Color.Black, false);
}
else if (datarow[4].ToString() == "Yellow")
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 5], excelSheet.Cells[rowcount, 5]];
FormattingExcelCells(excelCellrange, "#ffff80", System.Drawing.Color.Black, false);
}
else if (datarow[4].ToString() == "Red")
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 5], excelSheet.Cells[rowcount, 5]];
FormattingExcelCells(excelCellrange, "#ff6666", System.Drawing.Color.Black, false);
}
} // end of foreach datarow
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[2, 1], excelSheet.Cells[rowcount, finalColumn]];
excelCellrange.WrapText = true;
excelCellrange.Columns[1].ColumnWidth = 8;
excelCellrange.Columns[2].ColumnWidth = 15;
excelCellrange.Columns[3].ColumnWidth = 20;
excelCellrange.Columns[4].ColumnWidth = 38;
excelCellrange.Columns[5].ColumnWidth = 8;
excelCellrange.Columns[6].ColumnWidth = 11;
excelCellrange.Columns[7].ColumnWidth = 13;
excelCellrange.Columns[8].ColumnWidth = 15;
excelCellrange.Columns[9].ColumnWidth = 15;
excelCellrange.Columns[10].ColumnWidth = 10;
excelCellrange.Columns[11].ColumnWidth = 11;
excelCellrange.Columns[12].ColumnWidth = 32;
excelCellrange.Columns[13].ColumnWidth = 33;
excelCellrange.Columns[14].ColumnWidth = 0;
excelCellrange.Columns[15].ColumnWidth = 15;
excelCellrange.Columns[16].ColumnWidth = 10;
// change vertical align to top
excelSheet.Range[excelSheet.Cells[2, 1], excelSheet.Cells[rowcount, finalColumn]].VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
//excelCellrange = excelSheet.Range[excelSheet.Cells[2, 1], excelSheet.Cells[rowcount, finalColumn]];
//excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
//FormattingExcelCells(excelCellrange, "#ffffff", System.Drawing.Color.Black, false);
// make column names bold
excelSheet.Cells[1, 1].EntireRow.Font.Bold = true;
excelSheet.Cells[1, 1].EntireRow.Font.Size = 13;
excelSheet.Cells[2, 1].EntireRow.Font.Bold = true;
excelSheet.Cells[2, 1].EntireRow.RowHeight = 40;
excelSheet.Cells[2, 1].EntireRow.Font.Size = 13;
//auto filter on
//excelCellrange = excelSheet.Range[excelSheet.Cells[2, 1], excelSheet.Cells[rowcount, finalColumn]];
excelCellrange.Cells.AutoFilter(1, Type.Missing, XlAutoFilterOperator.xlAnd, Type.Missing, true);
//excelSheet.Cells.AutoFilter(1, Type.Missing, XlAutoFilterOperator.xlAnd, Type.Missing, true);
DateTime today = DateTime.Today;
string addToName = today.ToString("yyyyMMdd") + ".xlsx";
exportTempLocation = exportTempLocation + addToName;
fileName = fileName + addToName;
// set print header and footer and other page setup options
excelSheet.PageSetup.LeftHeader = "Tier 1 ICG Project Status";
excelSheet.PageSetup.LeftFooter = "Last Printed: &D &T";
excelSheet.PageSetup.CenterFooter = fileName;
excelSheet.PageSetup.RightFooter = "Page &P of &N";
excelSheet.PageSetup.PaperSize = XlPaperSize.xlPaperLegal;
excelSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
excelSheet.PageSetup.Zoom = false;
excelSheet.PageSetup.FitToPagesWide = 1;
excelSheet.PageSetup.FitToPagesTall = false;
excelSheet.PageSetup.PrintTitleRows = "$1:$2";
//excelSheet.PageSetup.PrintTitleRows = "$2:$2";
excelSheet.PageSetup.TopMargin = 36; // 36 points is .5"
excelSheet.PageSetup.BottomMargin = 36;
excelSheet.PageSetup.LeftMargin = 36;
excelSheet.PageSetup.RightMargin = 36;
//now save the workbook and exit Excel
//Console.WriteLine("file location is: " + exportTempLocation);
try
{
excelworkBook.SaveAs(exportTempLocation);
//Console.WriteLine("Should have just saved Excel file to: " + exportTempLocation);
}
catch (Exception ex)
{
Console.WriteLine("Catch in saving excel file: " + ex.Message + ". " + ex.InnerException + ". " + ex.StackTrace);
}
return(true);
}
catch (Exception ex)
{
Console.WriteLine("Catch in Excel file creation: " + ex.InnerException + ". " + ex.StackTrace);
return(false);
}
finally
{
excelworkBook.Close();
excel.Quit();
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public void ExportFileFromDataTable_Dung(DataTable dt, string fileName, string title, string[] prm)
{
//set properties for SaveFileDilog
SaveFileDialog sfdSave = new SaveFileDialog();
sfdSave.Filter = "Excel file(*.xls)|*.xls";
sfdSave.Title = "Save to Excel file";
sfdSave.FileName = fileName;
//proccess save file excel
if (sfdSave.ShowDialog() == DialogResult.OK)
{
//create file infomation
FileInfo f = new FileInfo(sfdSave.FileName);
//delete if file exists
if (f.Exists == true)
{
f.Delete();
}
//Create excel file
ExcelCOM.Application exApp = new ExcelCOM.Application();
ExcelCOM.Workbook exBook = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet);
ExcelCOM.Worksheet exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1];
//Microsoft.Office.Interop.Excel.Range excelCellrange;
ExcelCOM.Range excelCellrange;
exSheet.Name = fileName;
//import Caption
excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[1, dt.Columns.Count]];
exSheet.get_Range((object)exSheet.Cells[1, 1], (object)exSheet.Cells[1, dt.Columns.Count]).Merge(false);
excelCellrange = exSheet.get_Range((object)exSheet.Cells[1, 1], (object)exSheet.Cells[1, dt.Columns.Count]);
excelCellrange.FormulaR1C1 = title;
excelCellrange.HorizontalAlignment = 3;
excelCellrange.VerticalAlignment = 3;
excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
excelCellrange.NumberFormat = "@";
//import header
int col = 1;
for (int i = 1; i <= dt.Columns.Count; i++)
{
exSheet.Cells[2, col] = dt.Columns[i - 1].Caption;
col++;
}
//import data
//int row = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
//row++;
col = 1;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (prm != null)
{
//exSheet.Cells[i + 3, col].NumberFormat = "@";
exSheet.Cells[i + 3, col] = dt.Rows[i][j].ToString();
}
col++;
}
}
// now we resize the columns
excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
//excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[1, dt.Columns.Count]];
//FormattingExcelCells(excelCellrange, "#f5f5f5", System.Drawing.Color.DarkBlue, true);
//excelCellrange = exSheet.Range[exSheet.Cells[2, 1], exSheet.Cells[2, dt.Columns.Count]];
//FormattingExcelCells(excelCellrange, "#0488a1", System.Drawing.Color.White, true);
exApp.Visible = false;
//
//exBook.CheckCompatibility = false;
//Save file excel into dictionary that you choose
exBook.SaveAs(sfdSave.FileName, ExcelCOM.XlFileFormat.xlWorkbookNormal,
null, null, false, false,
ExcelCOM.XlSaveAsAccessMode.xlExclusive,
false, false, false, false, false);
//close and release object
exBook.Close(false, false, false);
exApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
CustomMessageBox.MessageBox.ShowCustomMessageBox(Common.clsLanguages.GetResource("ExcelExport"),
Common.clsLanguages.GetResource("Information"),
Common.Config.CUSTOM_MESSAGEBOX_ICON.Information,
Common.Config.CUSTOM_MESSAGEBOX_BUTTON.OK);
}
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName">Name of Your Report</param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
private static bool WriteDataTableToExcel <T>(IEnumerable <T> list, string worksheetName, string saveAsLocation, string[] colum_names = null)
{
string ReporType = worksheetName;
System.Data.DataTable dataTable = CreateDataTable <T>(list);
if (colum_names != null)
{
dataTable = dataTable.DefaultView.ToTable(false, colum_names);
}
Application excel;
Workbook excelworkBook;
Worksheet excelSheet;
Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Interactive = false;
// for making Excel visible
//excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
excelSheet.Cells[1, 2] = "Date : " + HRcrud.DateTime_Now().ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 2;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 3)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);;
excelworkBook.Close();
excel.Quit();
return(true);
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
private int Generate_Summary_Data(Worksheet xlWorkSheet, int iStartRow)
{
// --------------------------------------- Summary Header ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Start : " + dttm_TranStart.Value.ToString("yyyy-MM-dd") + " " + dttm_TranStartTime.Value.ToString("HH:mm:ss");
iStartRow++;
// --------------------------------------- Summary Header ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "End : " + dttm_TranEnd.Value.ToString("yyyy-MM-dd") + " " + dttm_TranEndTime.Value.ToString("HH:mm:ss");
iStartRow = iStartRow + 2;
// --------------------------------------- Summary Header ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Sales Summary by Type";
iStartRow++;
int iStartSummaryRow = iStartRow;
// --------------------------------------- Summary Title ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Type";
xlWorkSheet.Cells[iStartRow, 2] = "Amount";
xlWorkSheet.Cells[iStartRow, 3] = "Tax";
xlWorkSheet.Cells[iStartRow, 4] = "Sum";
iStartRow++;
// --------------------------------------- Summary Title ---------------------------------
xlWorkSheet.Cells[iStartRow, 2] = "QTY";
xlWorkSheet.Cells[iStartRow, 3] = "GST";
xlWorkSheet.Cells[iStartRow, 4] = "PST";
iStartRow++;
DataAccessPOS dbPOS = new DataAccessPOS();
DataAccessPOS1 dbPOS1 = new DataAccessPOS1();
ordercomps = dbPOS1.Get_OrderComplete_by_Date_OrderBy_Type(dttm_TranStart.Value.ToString("yyyy-MM-dd"), dttm_TranStartTime.Value.ToString("HH:mm:ss"),
dttm_TranEnd.Value.ToString("yyyy-MM-dd"), dttm_TranEndTime.Value.ToString("HH:mm:ss"));
string strTypeName = "";
float iQTY = 0;
float iAmount = 0;
float iTax1 = 0;
float iTax2 = 0;
float iTax3 = 0;
float iTotal = 0;
float iTypeQTY = 0;
float iTypeAmount = 0;
float iTypeTax1 = 0;
float iTypeTax2 = 0;
float iTypeTax3 = 0;
float iTypeTotal = 0;
float iSumQTY = 0;
float iSumAmount = 0;
float iSumTax1 = 0;
float iSumTax2 = 0;
float iSumTax3 = 0;
float iSumTotal = 0;
float iDepositQTY = 0;
float iDepositAmount = 0;
float iDepositTax1 = 0;
float iDepositTax2 = 0;
float iDepositTax3 = 0;
float iDepositTotal = 0;
float iDiscountQTY = 0;
float iDiscountAmount = 0;
float iDiscountTax1 = 0;
float iDiscountTax2 = 0;
float iDiscountTax3 = 0;
float iDiscountTotal = 0;
int iTempTypeId = 0;
int n = 0;
if (ordercomps.Count > 0)
{
foreach (var ordcomp in ordercomps)
{
if (n == 0)
{
iTempTypeId = ordcomp.ProductTypeId;
}
if (iTempTypeId != ordcomp.ProductTypeId)
{
if (iTempTypeId != 0) // Except Deposit, Discount ... add first
{
// --------------------------------------- Type Summary ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = dbPOS.Get_ProductTypeName_By_Id(iTempTypeId);
xlWorkSheet.Cells[iStartRow, 2] = iTypeAmount.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = (iTypeTax1 + iTypeTax2 + iTypeTax3).ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iTypeTotal.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
;
xlWorkSheet.Cells[iStartRow, 2] = "( " + iTypeQTY.ToString("0") + " Ea)";
xlWorkSheet.Cells[iStartRow, 3] = iTypeTax1.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iTypeTax2.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
}
iTypeQTY = 0;
iTypeAmount = 0;
iTypeTax1 = 0;
iTypeTax2 = 0;
iTypeTax3 = 0;
iTypeTotal = 0;
iTempTypeId = ordcomp.ProductTypeId;
}
iQTY = ordcomp.Quantity;
iAmount = ordcomp.Amount;
iTax1 = ordcomp.Tax1;
iTax2 = ordcomp.Tax2;
iTax3 = ordcomp.Tax3;
iTotal = iAmount + iTax1 + iTax2 + iTax3;
iTypeQTY = iTypeQTY + iQTY;
iTypeAmount = iTypeAmount + iAmount;
iTypeTax1 = iTypeTax1 + iTax1;
iTypeTax2 = iTypeTax2 + iTax2;
iTypeTax3 = iTypeTax3 + iTax3;
iTypeTotal = iTypeTotal + iTotal;
iSumQTY = iSumQTY + iQTY;
iSumAmount = iSumAmount + iAmount;
iSumTax1 = iSumTax1 + iTax1;
iSumTax2 = iSumTax2 + iTax2;
iSumTax3 = iSumTax3 + iTax3;
iSumTotal = iSumTotal + iTotal;
/*Public Const CON_TRAN_CATEGORY_NAME_0 As String = "General"
* Public Const CON_TRAN_CATEGORY_NAME_1 As String = "Deposit"
* Public Const CON_TRAN_CATEGORY_NAME_2 As String = "Recycling Fee"
* Public Const CON_TRAN_CATEGORY_NAME_3 As String = "Chill Charge"
* Public Const CON_TRAN_CATEGORY_NAME_4 As String = "Discount"
* Public Const CON_TRAN_CATEGORY_NAME_5 As String = "Free Ticket"
* Public Const CON_TRAN_CATEGORY_NAME_6 As String = "Rounding" */
if (ordcomp.OrderCategoryId == 1) // Deposit
{
iDepositQTY = iDepositQTY + iQTY;
iDepositAmount = iDepositAmount + iAmount;
iDepositTax1 = iDepositTax1 + iTax1;
iDepositTax2 = iDepositTax2 + iTax2;
iDepositTax3 = iDepositTax3 + iTax3;
iDepositTotal = iDepositTotal + iTotal;
}
if (ordcomp.OrderCategoryId == 4) // Discount
{
iDiscountQTY = iDiscountQTY + iQTY;
iDiscountAmount = iDiscountAmount + iAmount;
iDiscountTax1 = iDiscountTax1 + iTax1;
iDiscountTax2 = iDiscountTax2 + iTax2;
iDiscountTax3 = iDiscountTax3 + iTax3;
iDiscountTotal = iDiscountTotal + iTotal;
}
n++;
}
if (iDepositQTY > 0)
{
// --------------------------------------- Deposit Summary ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Deposit";
xlWorkSheet.Cells[iStartRow, 2] = iDepositAmount.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = (iDepositTax1 + iDepositTax2 + iDepositTax3).ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iDepositTotal.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
xlWorkSheet.Cells[iStartRow, 2] = "( " + iDepositQTY.ToString("0") + " Ea)";
xlWorkSheet.Cells[iStartRow, 3] = iDepositTax1.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iDepositTax2.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
}
if (iDiscountQTY > 0)
{
// --------------------------------------- Discount Summary ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Discount";
xlWorkSheet.Cells[iStartRow, 2] = iDiscountAmount.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = (iDiscountTax1 + iDiscountTax2 + iDiscountTax3).ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iDiscountTotal.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
xlWorkSheet.Cells[iStartRow, 2] = "( " + iDiscountQTY.ToString("0") + " Ea)";
xlWorkSheet.Cells[iStartRow, 3] = iDiscountTax1.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iDiscountTax2.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
}
// --------------------------------------- TOTAL ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "TOTAL";
xlWorkSheet.Cells[iStartRow, 2] = iSumAmount.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = (iSumTax1 + iSumTax2 + iSumTax3).ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iSumTotal.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
xlWorkSheet.Cells[iStartRow, 2] = "( " + iSumQTY.ToString("0") + " Ea)";
xlWorkSheet.Cells[iStartRow, 3] = iSumTax1.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iSumTax2.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
//iStartRow++;
}
// --------------------------------------- Set Boder ---------------------------------
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("A" + iStartSummaryRow.ToString(), "D" + iStartRow.ToString());
formatRange.Font.Size = 8;
Excel.Borders border = formatRange.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);
// --------------------------------------- Set Boder on title ---------------------------------
formatRange = xlWorkSheet.get_Range("A" + iStartSummaryRow.ToString(), "D" + (iStartSummaryRow + 1).ToString());
formatRange.EntireRow.Font.Bold = true;
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);
// --------------------------------------- Set Boder on Total ---------------------------------
formatRange = xlWorkSheet.get_Range("A" + (iStartRow - 1).ToString(), "D" + (iStartRow).ToString());
formatRange.EntireRow.Font.Bold = true;
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);
iStartRow++;
return(iStartRow);
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName">Name of Your Report</param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
private static string ExcelTemplate(List <Columns_Excel> columns, string worksheetName, string excelPath)
{
Application xlApp;
Workbook xlWorkBook;
Worksheet xlWorkSheet;
//object misValue = System.Reflection.Missing.Value;
int u = 50;//columns.Count + 40;
try
{
// Start Excel and get Application object.
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Interactive = false;
// for making Excel visible
//excel.Visible = false;
xlApp.DisplayAlerts = false;
// Creation a new Workbook
xlWorkBook = xlApp.Workbooks.Add(Type.Missing);
// Workk sheet
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.ActiveSheet;
xlWorkSheet.Name = worksheetName;
// //Previous code was referring to the wrong class, throwing an exception
// xlApp = new Application();
//// xlApp.DefaultSaveFormat = XlFileFormat.xlOpenXMLWorkbook;
// xlWorkBook = xlApp.Workbooks.Add(Type.Missing);
// // xlWorkBook = xlApp.Workbooks.Add(misValue);
// xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);
// xlApp.Interactive = false;
// // for making Excel visible
// // xlApp.Visible = false;
// xlApp.DisplayAlerts = false;
char[] alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
for (int i = 0; i <= columns.Count - 1; i++)
{
xlWorkSheet.Cells[1, i + 1] = columns[i].Colum_name;
if (columns[i].DropDown_List != null)
{
if (columns[i].DropDown_List.Count != 0)
{
// Columns[y].excel_col_order_name = B ---->i need it in B:B format
string col_name_format = alpha[i] + ":" + alpha[i];
var drop_down_range = xlWorkSheet.Columns[col_name_format, Type.Missing];
// var my_range = xlWorkSheet.Range[xlWorkSheet.Cells[1, u], xlWorkSheet.Cells[columns[i].DropDown_List.Count, u]];
Microsoft.Office.Interop.Excel.Range my_range = xlWorkSheet.Range[xlWorkSheet.Cells[1, u], ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[columns[i].DropDown_List.Count, u])];
int y = 0;
foreach (var item in columns[i].DropDown_List)
{
xlWorkSheet.Cells[y + 1, u] = item;
y++;
}
xlWorkSheet.Names.Add("Range_" + u, my_range);
//var flatList = string.Join(",", columns[1].DropDown_List);
drop_down_range.Validation.Delete();
drop_down_range.Validation.Add(
XlDVType.xlValidateList,
XlDVAlertStyle.xlValidAlertInformation,
XlFormatConditionOperator.xlBetween,
"=Range_" + u,
Type.Missing);
drop_down_range.Validation.InCellDropdown = true;
}
}
FormattingExcelCells(xlWorkSheet.Cells[1, i + 1], columns[i].color, columns[i].Font_Color, true);
xlWorkSheet.Columns[i + 1].ColumnWidth = columns[i].Width;
u++;
}
// now we resize the columns
Range excelCellrange = xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, columns.Count]];
//excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
border.Color = 0xFFFFFF;
xlWorkBook.SaveAs(excelPath);
xlWorkBook.Close(true);
xlApp.Quit();
// releaseObject(xlApp);
// releaseObject(xlWorkBook);
// releaseObject(xlWorkSheet);
return("");
}
catch (Exception ex)
{
return(ex.ToString());
}
}
private int Generate_Tender_Summary_Data(Worksheet xlWorkSheet, int iStartRow)
{
// --------------------------------------- Summary Header ---------------------------------
iStartRow++;
xlWorkSheet.Cells[iStartRow, 1] = "Tender Summary";
iStartRow++;
int iStartSummaryRow = iStartRow;
// --------------------------------------- Summary Title ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "Tender";
xlWorkSheet.Cells[iStartRow, 2] = "Amount";
xlWorkSheet.Cells[iStartRow, 3] = "Tip";
xlWorkSheet.Cells[iStartRow, 4] = "Total";
iStartRow++;
DataAccessPOS dbPOS = new DataAccessPOS();
DataAccessPOS1 dbPOS1 = new DataAccessPOS1();
trancols = dbPOS1.Get_TranCollection_by_DateTimeRange(dttm_TranStart.Value.ToString("yyyy-MM-dd"), dttm_TranStartTime.Value.ToString("HH:mm:ss"),
dttm_TranEnd.Value.ToString("yyyy-MM-dd"), dttm_TranEndTime.Value.ToString("HH:mm:ss"));
string[] strColTypeName = new string[] { "Cash", "Debit", "Visa", "MasterCard", "Amex", "GiftCard" };
float[] iQTY = new float[] { 0, 0, 0, 0, 0, 0 };
float[] iNetAmount = new float[] { 0, 0, 0, 0, 0, 0 };
float[] iTip = new float[] { 0, 0, 0, 0, 0, 0 };
float[] iTotal = new float[] { 0, 0, 0, 0, 0, 0 };
float iTotalQTY = 0;
float iTotalNetAmount = 0;
float iTotalTip = 0;
float iTotalTotal = 0;
string strTemp = "";
int n = 0;
if (trancols.Count > 0)
{
foreach (var trancol in trancols)
{
for (int i = 0; i < strColTypeName.Length; i++)
{
if (trancol.CollectionType == strColTypeName[i])
{
iQTY[i]++;
iNetAmount[i] = iNetAmount[i] + trancol.TotalPaid;
iTip[i] = iTip[i] + trancol.TotalTip;
iTotal[i] = iTotal[i] + (trancol.TotalPaid + trancol.TotalTip);
iTotalQTY++;
iTotalNetAmount = iTotalNetAmount + trancol.TotalPaid;
iTotalTip = iTotalTip + trancol.TotalTip;
iTotalTotal = iTotalTotal + (trancol.TotalPaid + trancol.TotalTip);;
}
}
}
}
for (int i = 0; i < strColTypeName.Length; i++)
{
if (iQTY[i] > 0)
{
// --------------------------------------- Tender ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = strColTypeName[i] + " ( " + iQTY[i].ToString() + " )";
xlWorkSheet.Cells[iStartRow, 2] = iNetAmount[i].ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = iTip[i].ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iTotal[i].ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
iStartRow++;
}
}
// --------------------------------------- Tender ---------------------------------
xlWorkSheet.Cells[iStartRow, 1] = "TOTAL" + " (" + iTotalQTY.ToString() + " )";
xlWorkSheet.Cells[iStartRow, 2] = iTotalNetAmount.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 3] = iTotalTip.ToString("0.00");
xlWorkSheet.Cells[iStartRow, 4] = iTotalTotal.ToString("0.00");
//xlWorkSheet.get_Range("c" + iStartRow.ToString(), "d" + iStartRow.ToString()).Merge(false);
// --------------------------------------- Set Boder ---------------------------------
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("A" + iStartSummaryRow.ToString(), "D" + iStartRow.ToString());
formatRange.Font.Size = 8;
Excel.Borders border = formatRange.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);
// --------------------------------------- Set Boder on title ---------------------------------
formatRange = xlWorkSheet.get_Range("A" + iStartSummaryRow.ToString(), "D" + iStartSummaryRow.ToString());
formatRange.EntireRow.Font.Bold = true;
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);
// --------------------------------------- Set Boder on Total ---------------------------------
formatRange = xlWorkSheet.get_Range("A" + (iStartRow).ToString(), "D" + (iStartRow).ToString());
formatRange.EntireRow.Font.Bold = true;
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);
iStartRow++;
return(iStartRow);
}
private void adxExcelAppEvents1_SheetSelectionChange(object sender, object sheet, object range)
{
Excel.Range selectedRange = null;
Excel.Range precedentsRange = null;
//Excel.Range prevSelectedRange = null;
if (autoFormHighlightBool == true)
{
autoFormHighlightBool = false;
try
{
selectedRange = range as Excel.Range;
Debug.Print("SELECTED RANGE:");
Debug.Print(selectedRange.Address);
precedentsRange = selectedRange.DirectPrecedents;
rangeStore2 = selectedRange.DirectPrecedents;
cellArray1 = new Excel.Range[selectedRange.DirectPrecedents.Count];
int i = 0;
foreach (Excel.Range rng in selectedRange.DirectPrecedents.Cells)
{
cellArray1[i] = rng;
i++;
Debug.WriteLine(rng.Address);
}
//selectedRange = ExcelApp.Selection as Excel.Range;
//precedentsRange = rootCell.DirectPrecedents ?? null;
}
catch
{
precedentsRange = null;
rangeStore2 = null;
}
try
{
if (selectedRange != null && precedentsRange != null)
{
if (autoFormHighlightFirstSelectionBool == true)
{
autoFormHighlightFirstSelectionBool = false; //this bool should be true only for the first selection
}
else
{
try
{
for (int i = 0; i < cellArray2.Length; i++)
{
if (colorStoreArray1[i] == Color.White)
{
cellArray2[i].Interior.Color = -4142;
}
else
{
cellArray2[i].Interior.Color = colorStoreArray1[i];
}
}
}
catch
{
}
}
colorStoreArray1 = new Color[cellArray1.Length];
for (int i = 0; i < cellArray1.Length; i++)
{
colorStoreArray1[i] = ColorTranslator.FromOle((int)((double)cellArray1[i].Interior.Color));
}
colorStore1 = ColorTranslator.FromOle((int)((double)precedentsRange.Interior.Color)); //interior color first cast as double, then int, then converted to System.Color
colorStore2 = ColorTranslator.FromOle((int)((double)precedentsRange.Borders.Color));
tintStore1 = Convert.ToDouble(precedentsRange.Interior.TintAndShade);
borderStore1 = precedentsRange.Borders;
rangeStore1 = rangeStore2;
cellArray2 = new Excel.Range[cellArray1.Length];
Array.Copy(cellArray1, cellArray2, cellArray1.Length);
precedentsRange.Interior.Color = Color.FromArgb(1, 1, 1, 255);
precedentsRange.Interior.TintAndShade = 0.7;
}
else
{
for (int i = 0; i < cellArray2.Length; i++)
{
if (colorStoreArray1[i] == Color.White)
{
cellArray2[i].Interior.Color = -4142;
}
else
{
cellArray2[i].Interior.Color = colorStoreArray1[i];
}
}
}
}
catch
{
}
finally
{
if (selectedRange != null)
{
Marshal.ReleaseComObject(selectedRange); //not sure why needs to be commented out
}
if (precedentsRange != null)
{
Marshal.ReleaseComObject(precedentsRange);
}
}
autoFormHighlightBool = true;
}
else
{
if (cellArray2 != null && cellArray2.Length > 0)
{
for (int i = 0; i < cellArray2.Length; i++)
{
if (colorStoreArray1[i] == Color.White)
{
cellArray2[i].Interior.Color = -4142;
}
else
{
cellArray2[i].Interior.Color = colorStoreArray1[i];
}
}
}
}
}
private Worksheet AgregarDatosFilasColumnas(Worksheet worksheet, int CantidadColumnas, int cantidadFilas, IEnumerable <SeccionModels> datosSeccion, int tipoSeccion)
{
if (tipoSeccion == 4)
{
CantidadColumnas = CantidadColumnas / 2;
}
//Obtiene cada cuantas columnas se hara un borde en la fila
int CantidadSumarColumnas = UltimaColumna / CantidadColumnas;
//Incializamos la Columna Separadora Final
ColummaSeparadoraFinal = CantidadSumarColumnas;
//falta verificar si es para o no para la cantidad de columnas actuales y si se puede dividir entre cantidad de columnas requeridas
int contadorDatos = 1;
//Agrega por tipo de dato
for (int i = 0; i < cantidadFilas; i++)
{
int contadorDatosPorColumnas = 0;
for (int j = 0; j < CantidadColumnas; j++)
{
if (contadorDatosPorColumnas < CantidadColumnas)
{
//Coloca el borde sobre las columnas visibles
foreach (var obj in datosSeccion)
{
SeccionModels seccion = obj;
if (seccion.NumeroOrden == contadorDatos)
{
//define el rango para crear las columnas visibles
switch (tipoSeccion)
{
case 1:
Excel.Range rangocolumnasvisibles = worksheet.Range[worksheet.Cells[ultimaFila, ColumnaSeparadoraInicial], worksheet.Cells[ultimaFila, ColummaSeparadoraFinal]];
rangocolumnasvisibles.Merge();
rangocolumnasvisibles.Columns.AutoFit();
worksheet.Cells[ultimaFila, ColummaSeparadoraFinal - 1] = seccion.Titulo + " " + seccion.ValorDato;
Excel.Borders border = rangocolumnasvisibles.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
SetCellFirstWordBold(worksheet.Cells[ultimaFila, ColummaSeparadoraFinal - 1], ':');
break;
case 2:
break;
case 3:
//Asigna valores
worksheet.Cells[ultimaFila, ColummaSeparadoraFinal] = seccion.NumeroOrden;
worksheet.Cells[ultimaFila, ColummaSeparadoraFinal + 1] = seccion.Titulo;
//Establece Estilos
contadorDatosPorColumnas++;
ColummaSeparadoraFinal++;
Excel.Range rango221 = worksheet.Range[worksheet.Cells[ultimaFila, ColummaSeparadoraFinal - 1], worksheet.Cells[ultimaFila, ColummaSeparadoraFinal - 1]];
Excel.Range rango222 = worksheet.Range[worksheet.Cells[ultimaFila, ColummaSeparadoraFinal], worksheet.Cells[ultimaFila, ColummaSeparadoraFinal]];
rango221.Columns.AutoFit();
rango221.Merge();
rango222.Columns.AutoFit();
rango222.Merge();
//Coloca Bordes
Excel.Borders border221 = rango221.Borders;
Excel.Borders border222 = rango222.Borders;
border221.LineStyle = Excel.XlLineStyle.xlContinuous;
border222.LineStyle = Excel.XlLineStyle.xlContinuous;
break;
case 4:
Excel.Range celdaDescripcion = worksheet.Range[worksheet.Cells[ultimaFila, ColumnaSeparadoraInicial], worksheet.Cells[ultimaFila, ColummaSeparadoraFinal]];
celdaDescripcion.Merge();
//Establece Estilos
celdaDescripcion.Style.WrapText = true;
worksheet.Cells[ultimaFila, ColummaSeparadoraFinal - 1] = seccion.Descripcion;
celdaDescripcion.EntireRow.AutoFit();
celdaDescripcion.EntireRow.RowHeight = 60;
celdaDescripcion.VerticalAlignment = XlVAlign.xlVAlignCenter;
//Coloca bordes
Excel.Borders border223 = celdaDescripcion.Borders;
border223.LineStyle = Excel.XlLineStyle.xlContinuous;
break;
}
//incrementa valores para la proxima columna visible
ColumnaSeparadoraInicial = ColummaSeparadoraFinal + 1;
ColummaSeparadoraFinal = ColummaSeparadoraFinal + CantidadSumarColumnas;
contadorDatos++;
contadorDatosPorColumnas++;
}
if (contadorDatosPorColumnas >= CantidadColumnas)
{
break;
}
}
}
else
{
//contadorDatos = 1;
contadorDatosPorColumnas = 0;
break;
}
}
ColumnaSeparadoraInicial = 1;
ColummaSeparadoraFinal = CantidadSumarColumnas;
ultimaFila++;
}
ColummaSeparadoraFinal = 0;
ColumnaSeparadoraInicial = 1;
return(worksheet);
}
private void button2_Click(object sender, EventArgs e)
{
app.Application exapp = new app.Application();
app.Workbook ebook = exapp.Workbooks.Add(app.XlWBATemplate.xlWBATWorksheet);
app.Worksheet exSheet = (app.Worksheet)ebook.Worksheets[1];
exSheet.Activate();
exSheet.Name = "DLDN";
List <CSVModel> ls = new List <CSVModel>();
ls = controller.CSVExport();
int j = 1;
for (int i = 0; i < ls.Count; i++)
{
{
String a1merge = "A" + j.ToString() + ":B" + j.ToString();
exSheet.Range[a1merge].Merge();
app.Range r = exSheet.Cells[j, 1];
r.Value = ls[i].RoomName;
a1merge = "C" + j.ToString() + ":D" + j.ToString();
exSheet.Range[a1merge].Merge();
app.Range r1 = exSheet.Cells[j, 3];
r1.Value = DateTime.Now;
r1.Columns.AutoFit();
a1merge = "A" + (j + 1).ToString() + ":B" + (j + 1).ToString();
exSheet.Range[a1merge].Merge();
app.Range r2 = exSheet.Cells[j + 1, 1];
r2.Value = "Điện";
a1merge = "C" + (j + 1).ToString() + ":D" + (j + 1).ToString();
exSheet.Range[a1merge].Merge();
app.Range r3 = exSheet.Cells[j + 1, 3];
r3.Value = "Nước";
app.Range r4 = exSheet.Cells[j + 2, 1];
r4.Value = "Cũ: " + ls[i].OldElect.ToString();
app.Range r5 = exSheet.Cells[j + 2, 2];
r5.Value = "Mới: " + ls[i].NewElect.ToString();
app.Range r6 = exSheet.Cells[j + 2, 3];
r6.Value = "Cũ: " + ls[i].OldWater.ToString();
app.Range r7 = exSheet.Cells[j + 2, 4];
r7.Value = "Mới: " + ls[i].NewWater.ToString();
a1merge = "A" + (j + 3).ToString() + ":B" + (j + 3).ToString();
exSheet.Range[a1merge].Merge();
app.Range r8 = exSheet.Cells[j + 3, 1];
r8.Value = ls[i].TotalElect.ToString() + " x 3000 = " + ls[i].MoneyElect.ToString();
a1merge = "C" + (j + 3).ToString() + ":D" + (j + 3).ToString();
exSheet.Range[a1merge].Merge();
app.Range r9 = exSheet.Cells[j + 3, 3];
r9.Value = ls[i].TotalWater.ToString() + " x 5000 = " + ls[i].MoneyWater.ToString();
a1merge = "A" + (j + 4).ToString() + ":D" + (j + 4).ToString();
exSheet.Range[a1merge].Merge();
a1merge = "A" + (j + 5).ToString() + ":D" + (j + 5).ToString();
exSheet.Range[a1merge].Merge();
app.Range r0 = exSheet.Cells[j + 5, 1];
r0.Value = "Tổng cộng = " + ls[i].Total.ToString();
a1merge = "A" + j.ToString() + ":D" + (j + 5).ToString();
app.Borders borders = exSheet.Range[a1merge].Borders;
borders.LineStyle = app.XlLineStyle.xlContinuous;
borders.Weight = 2d;
exSheet.Range[a1merge].HorizontalAlignment = 3;
exSheet.Range[a1merge].VerticalAlignment = 3;
//var b = exSheet.Range[a1merge];
//b.BorderAround2(app.XlLineStyle.xlContinuous, app.XlBorderWeight.xlMedium, app.XlColorIndex.xlColorIndexAutomatic, app.XlColorIndex.xlColorIndexAutomatic);
}
j += 7;
}
//app.Range r = (app.Range)exSheet.Cells[1, 1];
//r.Value = "Demo excel value";
//r.Columns.AutoFit();
exapp.Visible = true;
}
private void CreateDocument()
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
app.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"C:\LPA_IADE_V2\LPA_IADE.xlsx",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet ws = wb.Worksheets[1];
int col = 1;
int row = 16;
ws.Range["B8"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B9"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B10"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B11"].Value = Convert.ToString(replaceData).TrimStart();
GetNotas();
foreach (Notas nota in listaNotas)
{
ws.Cells[row, col].Font.Size = 10;
ws.Cells[row, col].Value = nota.UC.TrimStart();
ws.Cells[row, col + 5].Font.Size = 10;
ws.Cells[row, col + 5].Value = nota.Nota;
ws.Cells[row, col + 6].Font.Size = 10;
ws.Cells[row, col + 6].Value = nota.ECTS;
row++;
}
GetMedia((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["B59"].Value = (string)replaceMedia + " valores";
ws.Range["B73"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B74"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B75"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B76"].Value = Convert.ToString(replaceData).TrimStart();
Competencias competencias = GetDadosCompetencias((string)replaceNrAluno);
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(5, 1165, 470, 300);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chartPage.SeriesCollection();
var ser = seriesCollection.NewSeries();
chartPage.Legend.Delete();
ser.Values = new double[] { competencias.adaptacao, competencias.trabalhar, competencias.decisoes, competencias.objactivos, competencias.ideias, competencias.aprendizagem, competencias.mentalidadeGlobal, competencias.gestaoEquipas };
ser.XValues = new string[] { "Adaptação", "Trabalhar com os Outros", "Tomar Decisões", "Alcançar Objetivos", "Geração de Ideias", "Aprendizagem", "Mentalidade Global", "Gestão de Equipas" };
//chartRange = ws.get_Range("A1", "d5");
//chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered;
chartPage.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(5).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(6).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(7).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(8).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
ws.Range["B127"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B128"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B129"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B130"].Value = Convert.ToString(replaceData).TrimStart();
GetLinguas((string)replaceNrAluno);
GetRespAcademicas((string)replaceNrAluno);
GetEstagios((string)replaceNrAluno);
GetMobilidade((string)replaceNrAluno);
GetPremios((string)replaceNrAluno);
GetAtividades((string)replaceNrAluno);
GetOutrasExperiencias((string)replaceNrAluno);
GetRespSocial((string)replaceNrAluno);
col = 1;
row = 135;
if (listaLinguas.Count > 0)
{
string header = "LÍNGUAS";
ws.Cells[row, col].Font.Color = XlRgbColor.rgbRed;
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Red;
foreach (Linguas l in listaLinguas)
{
row++;
ws.Cells[row, col].Value = " > " + l.lingua + " | Nível " + l.nivel;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespAcademicas.Count > 0)
{
string header = "RESPONSABILIDADES ACADÉMICAS";
ws.Cells[row, col].Font.Color = XlRgbColor.rgbRed;
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Red;
foreach (ResponsabilidadesAcademicas l in listaRespAcademicas)
{
row++;
ws.Cells[row, col].Value = " > " + l.responsabilidade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaEstagios.Count > 0)
{
string header = "ESTÁGIOS";
ws.Cells[row, col].Font.Color = XlRgbColor.rgbRed;
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Red;
foreach (Estagios l in listaEstagios)
{
if (l.dataInicio != "01/01/1900")
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa + " | De " + l.dataInicio.Substring(0, 10) + " a " + l.dataFim.Substring(0, 10);
}
else
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa;
}
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaMobilidade.Count > 0)
{
string header = "MOBILIDADE INTERNACIONAL";
ws.Cells[row, col].Font.Color = XlRgbColor.rgbRed;
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Red;
foreach (Mobilidade l in listaMobilidade)
{
row++;
ws.Cells[row, col].Value = " > " + l.tipo + " na " + l.programa + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespSocial.Count > 0)
{
string header = "RESPONSABILIDADE SOCIAL & VOLUNTARIADO";
ws.Cells[row, col].Font.Color = XlRgbColor.rgbRed;
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Red;
foreach (ResposabilidadeSocial l in listaRespSocial)
{
row++;
ws.Cells[row, col].Value = " > " + l.AcaoSocial + " | " + l.AnoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaPremios.Count > 0)
{
string header = "PRÉMIOS & RECONHECIMENTOS";
ws.Cells[row, col].Font.Color = XlRgbColor.rgbRed;
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Red;
foreach (Premios l in listaPremios)
{
row++;
ws.Cells[row, col].Value = " > " + l.premio + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaAtividades.Count > 0)
{
string header = "ATIVIDADES DESPORTIVAS";
ws.Cells[row, col].Font.Color = XlRgbColor.rgbRed;
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Red;
foreach (ActDesportivas l in listaAtividades)
{
row++;
ws.Cells[row, col].Value = " > " + l.atividade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaExperiencias.Count > 0)
{
string header = "OUTRAS EXPERIÊNCIAS";
ws.Cells[row, col].Font.Color = XlRgbColor.rgbRed;
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Red;
foreach (OutrasExperiencias l in listaExperiencias)
{
row++;
ws.Cells[row, col].Value = " > " + l.experiencia + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
string data = Convert.ToString(DateTime.Now.ToLongDateString());
ws.Cells[164, 1].Value = "Lisboa, " + data;
int nrCertificado = GravaCertificado(competencias);
string anoLetivo = getAnoLetivo((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["E11"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E76"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E130"].Value = nrCertificado + " | POR | " + anoLetivo;
}
/// <summary>
/// 设置连续多单元格块范围
/// </summary>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <param name="endCol"></param>
public void SetRange(int startRow, int startCol, int endRow, int endCol)
{
this._range = this._worksheet.Range[this._worksheet.Cells[startRow, startCol], this._worksheet.Cells[endRow, endCol]];
this._font = this._range.Font;
this._borders = this._range.Borders;
this._leftBorder = this._borders[Excel.XlBordersIndex.xlEdgeLeft];
this._topBorder = this._borders[Excel.XlBordersIndex.xlEdgeTop];
this._rightBorder = this._borders[Excel.XlBordersIndex.xlEdgeRight];
this._bottomBorder = this._borders[Excel.XlBordersIndex.xlEdgeBottom];
}
static void createExcel(Coll.DataTable daTable)
{
try
{
Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
Workbook worKbooK = excel.Workbooks.Add(Type.Missing);
Worksheet worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = "Permissions";
worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 8]].Merge();
worKsheeT.Cells[1, 1] = "Site Permisison Data";
worKsheeT.Cells.Font.Size = 15;
Range celLrangE = null;
int rowcount = 2;
foreach (Coll.DataRow datarow in daTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= daTable.Columns.Count; i++)
{
if (rowcount == 3)
{
worKsheeT.Cells[2, i] = daTable.Columns[i - 1].ColumnName;
worKsheeT.Cells.Font.Color = System.Drawing.Color.Black;
}
worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3)
{
if (i == daTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, daTable.Columns.Count]];
}
}
}
}
}
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, daTable.Columns.Count]];
celLrangE.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, daTable.Columns.Count]];
worKbooK.SaveAs("E:\\Testing.xlsx");;
worKbooK.Close();
excel.Quit();
}
catch (Exception ex)
{
Console.WriteLine("Error Found in " + ex.StackTrace + "and the error is" + ex.Message.ToString());
}
finally
{
//worKsheeT = null;
//celLrangE = null;
//worKbooK = null;
}
}