private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Sheet 1 content";
xlWorkBook.SaveAs("d:\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file d:\\csharp-Excel.xls");
}
public void Dispose()
{
try
{
if (mBook != null)
{
mBook.Close(false, MISSING, MISSING);
mBook = null;
}
}
catch {}
try
{
if (mSheet != null)
{
mSheet = null;
}
}
catch {}
try
{
if (mExcelApp != null)
{
mExcelApp.Quit();
mExcelApp = null;
}
}
catch {}
}
private static object[,] GetExcelData(string tempateID)
{
OpenFileDialog openFileDialog = new OpenFileDialog
{
Filter = "Excel file(*.xlsx)|*.xlsx",
Multiselect = false
};
string path;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
path = openFileDialog.FileName;
}
else
{
return(null);
}
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wkb = null;
object[,] result = null;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
wkb = OpenBook(excel, path);
excel.DisplayAlerts = false;
Excel.Range xlRange = null;
Excel.Worksheet sheet = wkb.Sheets[ExcelTemplate.GetTemplate(tempateID)];
if (sheet != null)
{
xlRange = sheet.UsedRange;
}
if (xlRange != null)
{
result = xlRange.Value2;
}
excel.Quit();
return(result);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
if (excel != null)
{
excel.Quit();
}
return(result);
}
}
private void button3_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
for (int j = 0; j < dataGridView1.Rows.Count - 1; j++)
{
dataGridView1.Rows.RemoveAt(j);
j--;
while (dataGridView1.Rows.Count == 0)
{
continue;
}
}
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks workbooks = app.Workbooks;
Excel.Workbook workbook = workbooks.Open(textBox1.Text);
Excel.Worksheet worksheet = workbook.ActiveSheet;
try
{
int rcount = worksheet.UsedRange.Rows.Count;
int i = 0;
//Initializing Columns
dataGridView1.ColumnCount = worksheet.UsedRange.Columns.Count;
for (i = 1; i < rcount; i++)
{
//dataGridView1.Rows.Add(worksheet.Cells[i + 1, 1].Value, worksheet.Cells[i + 1, 2].Value, worksheet.Cells[i + 1, 3].Value, worksheet.Cells[i + 1, 4].Value, worksheet.Cells[i + 1, 5].Value, worksheet.Cells[i + 1, 6].Value, worksheet.Cells[i + 1, 7].Value, worksheet.Cells[i + 1, 8].Value, worksheet.Cells[i + 1, 9].Value);
dataGridView1.Rows.Add(worksheet.Cells[i + 1, 1].Value, worksheet.Cells[i + 1, 2].Value, worksheet.Cells[i + 1, 3].Value, worksheet.Cells[i + 1, 4].Value, worksheet.Cells[i + 1, 5].Value);
}
workbook.Close();
app.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(worksheet);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
workbook.Close();
app.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(worksheet);
}
}
public static Excel_Table LoadFromFile(string FileName)
{
//Открываем файл Экселя
//Создаём приложение.
Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
//Открываем книгу.
Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(FileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Выбираем таблицу(лист).
Excel.Worksheet ObjWorkSheet;
ObjWorkSheet = (Excel.Worksheet)ObjWorkBook.Sheets[1];
// Создаём новый Excel_Table объект
Excel_Table ET = new Excel_Table();
var lastCell = ObjWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);// Находим последнюю ячейку.
ET.Table_Width = lastCell.Column;
ET.Table_Height = lastCell.Row;
// Настройка прогрессбара
Progress.Maximum = ET.Table_Width * ET.Table_Height + 2 * ET.Table_Height;
Progress.Process = "Считывание данных из Excel";
ET.list = new string[ET.Table_Width, ET.Table_Height]; // массив значений с листа равен по размеру листу
for (int i = 0; i < ET.Table_Width; i++) //по всем колонкам
{
for (int j = 1; j < ET.Table_Height; j++) // по всем строкам
{
ET.list[i, j] = ObjWorkSheet.Cells[j + 1, i + 1].Text.ToString(); //считываем текст в строку
Progress.Position++;
Application.DoEvents();
if (Progress.Abort)
{
ObjWorkBook.Close(false, Type.Missing, Type.Missing); //закрыть не сохраняя
//Удаляем приложение (выходим из экселя) - а то будет висеть в процессах!
ObjExcel.Quit();
return(ET);
}
}
}
ObjWorkBook.Close(false, Type.Missing, Type.Missing); //закрыть не сохраняя
//Удаляем приложение (выходим из экселя) - а то будет висеть в процессах!
ObjExcel.Quit();
Progress.Done = ET.Table_Width * ET.Table_Height;
return(ET);
}
/// <summary>
/// Close all Excel workbooks without saving, removing any references to the
/// excel app and perform garbage collection
/// </summary>
public static void TryQuitAndCleanupWithoutSaving()
{
if (_excelApp != null)
{
_excelApp.Workbooks.Cast <Workbook>().ToList().ForEach((wb) => wb.Close(false));
_excelApp.Quit();
while (Marshal.ReleaseComObject(_excelApp) > 0)
{
}
GC.Collect();
GC.WaitForPendingFinalizers();
_excelApp = null;
}
}
private bool ValidarExcel()
{
try
{
Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
Workbook xlsBook;
Sheets xlHojas;
xlsApp.DisplayAlerts = false;
xlsApp.Visible = false;
string PathAr = Path.Combine(System.Windows.Forms.Application.StartupPath, @"Resources\Excel\StepthManager.xlsx");
xlsBook = xlsApp.Workbooks.Open(PathAr);
xlHojas = xlsBook.Sheets;
xlsBook.Close(true);
xlsApp.Quit();
releaseObject(xlHojas);
releaseObject(xlsBook);
releaseObject(xlsApp);
return(true);
}
catch (Exception ex)
{
LogError.AddExcFileTxt(ex, "frmInventario ~ ValidarExcel");
return(false);
}
}
public void cargaBeneficiario(string Path, List <Educafin.Models.Alumno> lis)
{
System.IO.FileInfo fi = new System.IO.FileInfo(Path);
if (fi.Exists)
{
fi.Delete();
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 2;
xlWorkSheet.Cells[1, 1] = "Curp";
xlWorkSheet.Cells[1, 2] = "Matricula";
xlWorkSheet.Cells[1, 3] = "Apellido Paterno";
xlWorkSheet.Cells[1, 4] = "Apellido Materno";
xlWorkSheet.Cells[1, 5] = "Nombre(s)";
foreach (var item in lis)
{
xlWorkSheet.Cells[i, 1] = item.curp;
xlWorkSheet.Cells[i, 2] = item.matricula;
xlWorkSheet.Cells[i, 3] = item.apePat;
xlWorkSheet.Cells[i, 4] = item.apeMat;
xlWorkSheet.Cells[i, 5] = item.nombreTemp;
i++;
}
xlWorkBook.SaveAs(Path);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
public void ExportDTToExcel(System.Data.DataTable dt)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
// Headers.
for (int i = 0; i < dt.Columns.Count; i++)
{
ws.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
// Content.
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
ws.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
// Lots of options here. See the documentation.
wb.SaveAs("d:\\tsst.xlsx");
wb.Close();
app.Quit();
}
private void button3_Click(object sender, EventArgs e)
{
SaveFileDialog fichero = new SaveFileDialog();
fichero.Filter = "Excel (*.xls)|*.xls";
if (fichero.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application aplicacion;
Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
aplicacion = new Microsoft.Office.Interop.Excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
hoja_trabajo =
(Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
//Recorremos el DataGridView rellenando la hoja de trabajo
for (int i = 0; i < dtReporteGeneral.Rows.Count - 1; i++)
{
for (int j = 0; j < dtReporteGeneral.Columns.Count; j++)
{
hoja_trabajo.Cells[i + 1, j + 1] = dtReporteGeneral.Rows[i].Cells[j].Value.ToString();
}
}
libros_trabajo.SaveAs(fichero.FileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
}
//Excel.Application xlApp;
//Excel.Workbook xlWorkBook;
//Excel.Worksheet xlWorkSheet;
//object misValue = System.Reflection.Missing.Value;
//xlApp = new Excel.Application();
//xlWorkBook = xlApp.Workbooks.Add(misValue);
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//int i = 0;
//int j = 0;
//for (i = 0; i <= dtReporteGeneral.RowCount - 1; i++)
//{
// for (j = 0; j <= dtReporteGeneral.ColumnCount - 1; j++)
// {
// DataGridViewCell cell = dtReporteGeneral[j, i];
// xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
// }
//}
//xlWorkBook.SaveAs("Reporte_Historico.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//xlWorkBook.Close(true, misValue, misValue);
//xlApp.Quit();
//releaseObject(xlWorkSheet);
//releaseObject(xlWorkBook);
//releaseObject(xlApp);
//MessageBox.Show("Excel creado en = c:\\Reporte_Historico.xls");
}
private void Generate_PDFs()
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
object misValue = System.Reflection.Missing.Value;
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "AppNo";
xlWorkSheet.Cells[1, 2] = "Name";
xlWorkSheet.Cells[1, 3] = "Department";
xlWorkSheet.Cells[1, 4] = "Category";
xlWorkSheet.Cells[1, 5] = "Aggregate";
studentsSelectedList = studentsSelectedList.OrderByDescending(o => o.aggregate).ToList();
for (int i = 0; i < studentsSelectedList.Count(); i++)
{
//Setting width of the Excel Coloumns.
var studentinfo = studentsSelectedList[i];
xlWorkSheet.Columns[2].ColumnWidth = 18;
xlWorkSheet.Columns[3].ColumnWidth = 21;
xlWorkSheet.Columns[4].ColumnWidth = 18;
xlWorkSheet.Columns[5].ColumnWidth = 18;
//Setting information in Excel Workbook
xlWorkSheet.Cells[i + 2, 1] = studentinfo.appRefNum;
xlWorkSheet.Cells[i + 2, 2] = studentinfo.name;
xlWorkSheet.Cells[i + 2, 3] = studentinfo.preferenceList[0];
xlWorkSheet.Cells[i + 2, 4] = studentinfo.category;
xlWorkSheet.Cells[i + 2, 5] = studentinfo.aggregate;
}
//Saving as PDF
xlWorkBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, "C:\\Users\\hassa\\Desktop\\MeritListGenerator\\MeritListGenerator\\MeritList.pdf");
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
private void reSaveFile(string filePath)
{
object paramMissing = Type.Missing;
var app = new Microsoft.Office.Interop.Excel.Application();
app.Caption = System.Guid.NewGuid().ToString().ToUpper();
string ver = app.Version;
var wb = app.Workbooks.Open(filePath);
app.DisplayAlerts = false;
wb.SaveAs(filePath, FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook);
wb.Close(false, paramMissing, paramMissing);
app.Quit();
Config.EnsureProcessKilled(IntPtr.Zero, app.Caption);
Marshal.ReleaseComObject(wb);
Marshal.FinalReleaseComObject(wb);
wb = null;
Marshal.ReleaseComObject(app);
Marshal.FinalReleaseComObject(app);
app = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
public void cargaFormatoEntregasAnteriores(string Path)
{
System.IO.FileInfo fi = new System.IO.FileInfo(Path);
if (fi.Exists)
{
fi.Delete();
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "CURP";
xlWorkSheet.Cells[1, 2] = "CCT";
xlWorkSheet.Cells[1, 3] = "Matricula";
xlWorkSheet.Cells[1, 4] = "No. Serie";
xlWorkBook.SaveAs(Path);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
//导出Execl明细按钮点击事件
private void button1_Click(object sender, EventArgs e)
{
if (tower_list.Rows.Count <= 0)
{
MessageBox.Show("该表中没有需要导出的数据！", "提示");
return;
}
string fileName = "楼盘信息表";
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0)
{
return; //被点了取消
}
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for (int i = 0; i < tower_list.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = tower_list.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < tower_list.Rows.Count; r++)
{
for (int i = 0; i < tower_list.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = tower_list.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)//异常处理
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
}
public void WriteResultToExcelAttendance()
{
this.xlApp = new Microsoft.Office.Interop.Excel.Application();
if (this.xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
}
this.xlApp.Visible = false;
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(1);
Excel.Sheets xlWorksheet = xlWorkBook.Worksheets;
var xlNewSheet = (Excel.Worksheet)xlWorksheet.Add(xlWorksheet[1]);
int row = 1;
foreach (Attendance student in attendanceList)
{
xlNewSheet.Cells[row, 1] = student.StudentNo;
xlNewSheet.Cells[row, 2] = student.Name;
xlNewSheet.Cells[row, 3] = student.Visa;
xlNewSheet.Cells[row, 4] = student.NewWarning;
xlNewSheet.Cells[row, 5] = student.CourseCode;
xlNewSheet.Cells[row, 6] = student.StartDate;
xlNewSheet.Cells[row, 7] = student.EndDate;
xlNewSheet.Cells[row, 8] = student.CurrentAttendace;
xlNewSheet.Cells[row, 9] = student.OverallAttendace;
//foreach (Payment payment in payments)
//{
// xlNewSheet.Cells[row, 1] = student.StNo;
// if (payment.DueDate < System.DateTime.Today)
// {
// Excel.Range cell = xlNewSheet.Cells[row, 7];
// cell.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
// }
// xlNewSheet.Cells[row, 7] = payment.DueDate;
// xlNewSheet.Cells[row, 8] = payment.PaymentAmount;
// xlNewSheet.Cells[row, 9] = payment.Note;
row++;
//}
}
//Excel.Range range = xlNewSheet.UsedRange;
//range.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
String filename = xlApp.GetSaveAsFilename("result.xls", "Excel files (*.xls), *.xls");
if (string.IsNullOrEmpty(filename))
{
filename = "result.xls";
}
xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlWorkBook.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
MessageBox.Show("Done!!!!");
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 3] = "Weather forecast for the week ";
xlWorkSheet.Cells[2, 1] = listBox1.Items[0].ToString();
xlWorkSheet.Cells[2, 7] = listBox1.Items[6].ToString();
xlWorkSheet.Cells[3, 3] = label1.Text;
xlWorkBook.SaveAs("d:\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file d:\\csharp-Excel.xls");
}
private void button1_Click(object sender, EventArgs e)
{
try
{
saveFileDialog1.Title = "Save as Excel File";
saveFileDialog1.FileName = "";
saveFileDialog1.Filter = "Excel Files(2003)|*.xls|Excel Files(2007)|*.xls";
if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 20;
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
ExcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(saveFileDialog1.FileName.ToString());
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
static void Main(string[] args)
{
System.IO.StreamReader txtfile = new System.IO.StreamReader("D:\\Learning\\TestDLL\\Dllpath.txt");
int counter = txtfile.ReadLine().Count();
Console.WriteLine(counter);
Console.ReadLine();
Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
if (xlapp== null)
{
Console.WriteLine("Excel is not installed");
return;
}
Excel.Workbook xlworkbook;
Excel.Worksheet xlworksheet;
object misvalue = System.Reflection.Missing.Value;
xlworkbook = xlapp.Workbooks.Add(misvalue);
String filepath = @"D:\Learning\TestDLL\pdfshell.dll";
FileVersionInfo myFileVersionInfo = FileVersionInfo.GetVersionInfo(filepath);
xlworksheet=xlworkbook.Worksheets.get_Item(1);
xlworksheet.Name = "DLLProperties";
xlworksheet.Cells[1, 1] ="NameofFile" ;
xlworksheet.Cells[1, 2] = "ProductVersion";
xlworksheet.Cells.Font.Size = 25;
xlworkbook.SaveAs("D:\\Learning\\demoexcel3.xls", Excel.XlFileFormat.xlWorkbookNormal, misvalue, misvalue, misvalue, misvalue, Excel.XlSaveAsAccessMode.xlExclusive, misvalue, misvalue, misvalue, misvalue, misvalue);
xlworkbook.Close(true,misvalue,misvalue);
xlapp.Quit();
}
private void ExportarExcel(DataGridView grd)
{
SaveFileDialog fichero = new SaveFileDialog();
fichero.Filter = "Excel (*.xls)|*.xls";
if (fichero.ShowDialog() == DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Application aplicacion;
Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
aplicacion = new Microsoft.Office.Interop.Excel.Application();
libros_trabajo = aplicacion.Workbooks.Add();
hoja_trabajo =
(Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
//Recorremos el DataGridView rellenando la hoja de trabajo
for (int i = 0; i < grd.Rows.Count - 1; i++)
{
for (int j = 0; j < grd.Columns.Count; j++)
{
hoja_trabajo.Cells[i + 1, j + 1] = grd.Rows[i].Cells[j].Value.ToString();
}
}
libros_trabajo.SaveAs(fichero.FileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
libros_trabajo.Close(true);
aplicacion.Quit();
}
}
public static void Create(UserPasswordInfo userinfo)
{
//TODO exceli burada oluştur
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Application Name";
xlWorkSheet.Cells[1, 2] = "Email";
xlWorkSheet.Cells[1, 3] = "Username";
xlWorkSheet.Cells[2, 1] = userinfo.Application;
xlWorkSheet.Cells[2, 3] = userinfo.UserName;
xlWorkSheet.Cells[2, 2] = userinfo.Email;
xlApp.DisplayAlerts = false;
xlWorkBook.SaveAs("C:\\Users\\BaranOzsarac\\Documents\\EmailConfirmed_20191226_1340.xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
public void ExcelWrite(string path, string worksheet, int Row, int Column, List <object> Values)
{
try
{
List <string> output = new List <string>();
var excelApp = new Microsoft.Office.Interop.Excel.Application();
//Dont show Excel when open
excelApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks books = excelApp.Workbooks;
//Open Excel file by Path provided by the user
Microsoft.Office.Interop.Excel.Workbook sheet = books.Open(path);
//Select worksheet by the name provided by the user
Microsoft.Office.Interop.Excel.Worksheet indsheet = sheet.Sheets[worksheet];
Microsoft.Office.Interop.Excel.Range activecell = indsheet.Cells[Row, Column];
for (int i = 0; i < Values.Count; i += 1)
{
indsheet.Cells[Row + i, Column] = Values[i].ToString();
}
sheet.Close(true);
excelApp.Quit();
}
catch
{ }
}
private void BtnToExcel_Click(object sender, RoutedEventArgs e)
{
Microsoft.Office.Interop.Excel.Application application =
new Microsoft.Office.Interop.Excel.Application();
application.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet worksheet =
application.ActiveSheet;
worksheet.Cells[1, 1] = "Id_Worker";
worksheet.Cells[1, 2] = "FIO";
worksheet.Cells[1, 3] = "DateOfBirth";
worksheet.Cells[1, 4] = "Salary";
worksheet.Cells[1, 5] = "Position";
var cellRange = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 5]];
var allWorkers = AppData.Context.Workers.ToList();
for (int i = 0; i < allWorkers.Count(); i++)
{
var currentWorkers = allWorkers[i];
worksheet.Cells[i + 2, 1] = currentWorkers.Id_Worker;
worksheet.Cells[i + 2, 2] = currentWorkers.FIO;
worksheet.Cells[i + 2, 3] = currentWorkers.DateOfBirth.ToString("dd:MM:yyyy HH:mm");
worksheet.Cells[i + 2, 4] = currentWorkers.Salary;
worksheet.Cells[i + 2, 5] = currentWorkers.WorkersPosition.PositionName;
}
checkExportDirectory();
worksheet.SaveAs($"{AppDomain.CurrentDomain.BaseDirectory}Export" +
$"\\Export_Workers_XLSX_{DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss")}.xlsx");
application.Quit();
MessageBox.Show("Данные выгружены");
}
private void Button_Export_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.Application Exl = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb;
XlReferenceStyle RefStyle = Exl.ReferenceStyle;
Exl.Visible = false;
wb = Exl.Workbooks.Add(Type.Missing);
Worksheet ws = wb.Worksheets.get_Item(1) as Worksheet;
for (int j = 0; j < dataGrid_Glossary.Columns.Count; ++j)
{
for (int i = 0; i < dataGrid_Glossary.Rows.Count; ++i)
{
object Val = dataGrid_Glossary.Rows[i].Cells[j].Value;
if (Val != null)
{
(ws.Cells[i + 2, j + 1] as Range).Value2 = Val.ToString();
}
}
}
ws.Columns.EntireColumn.AutoFit();
Exl.ReferenceStyle = RefStyle;
Exl.Application.ActiveWorkbook.SaveAs("glossary.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Exl.Quit();
}
catch (ExternalException a)
{
Debug.WriteLine(a.Message);
}
}
public void Write(string path, object data)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
throw new Exception("PC chưa cài excel!");
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "ID";
xlWorkSheet.Cells[1, 2] = "Name";
xlWorkSheet.Cells[2, 1] = "1";
xlWorkSheet.Cells[2, 2] = "One";
xlWorkSheet.Cells[3, 1] = "2";
xlWorkSheet.Cells[3, 2] = "Two";
xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
}
public CreateExcel(DataGridView dgv, string excelPath)
{
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
// see the excel sheet behind the program
app.Visible = true;
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
// changing the name of active sheet
worksheet.Name = "Exported from gridview";
// storing header part in Excel
for (int i = 1; i < dgv.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dgv.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dgv.Rows.Count - 1; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value.ToString();
}
}
// save the application
workbook.SaveAs(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Exit from the application
app.Quit();
}
private void Button2_Click(object sender, EventArgs e)
{
Int64 todoist_project_id = 2215010764;
Int64 wunderlist_project_id = 401500612;
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook xlWorkbook = app.Workbooks.Open(@"C:\freigabe\aufgabenliste.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
for (int i = 2; i <= 4; i++)
{
String titel = xlRange.Cells[i, 1].Value2.ToString() + " | " + DateTime.Now.ToString("MM-dd-yyyy-HH-mm-ss");
double datum = double.Parse(xlRange.Cells[i, 2].Value2.ToString());
var dateTimeWunderlist = DateTime.FromOADate(datum).ToString("yyyy-MM-dd:hh:mm:ss");
var dateTimeTodoist = DateTime.FromOADate(datum).ToString("yyyy-MM-dd");
// MessageBox.Show(titel + " " + dateTime);
if (checkBoxWunderlist.Checked)
{
String resultWunderlistRequest = Wunderlist.CreateTask(wunderlist_project_id, titel, dateTimeWunderlist);
}
if (checkBoxTodoist.Checked)
{
String resultTodoistRequest = Todoist.CreateTask(todoist_project_id, titel, dateTimeTodoist);
}
}
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
app.Quit();
Marshal.ReleaseComObject(app);
}
public List <string> ExcelInfo(string path)
{
List <string> output = new List <string>();
try {
var excelApp = new Microsoft.Office.Interop.Excel.Application();
//Dont show Excel when open
excelApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks books = excelApp.Workbooks;
//Open Excel file by Path provided by the user
Microsoft.Office.Interop.Excel.Workbook sheet = books.Open(path);
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheet in sheet.Worksheets)
{
output.Add(worksheet.Name.ToString());
}
// accessing the desired worksheet in the dictionary
sheet.Close(true);
excelApp.Quit();
}
catch
{
//To be implemented
}
return(output);
}
private void 导出到ExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
string fileName;
saveFileDialog1.Filter = "Excel 2003 *.xls|*.xls|Excel 2007 *.xlsx|*.xlsx";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
fileName = saveFileDialog1.FileName;
Excel.Application app =
new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Workbook wBook = app.Workbooks.Add(true);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
DsToWsheet(ds, wSheet);
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
wBook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
app.Quit();
app = null;
statusBarShow("已成功导出到" + fileName);
}
}
private void WriteExcel(Dictionary <string, string> dict, string filename, int col = 2)
{
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
int row = 1;
foreach (var key in dict.Keys)
{
oSheet.Cells[row, 1] = key;
string keyVal;
if (dict.TryGetValue(key, out keyVal))
{
oSheet.Cells[row, col] = keyVal;
}
row++;
}
oWB.SaveAs("C:\\Users\\mahmad\\Desktop\\" + filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
oXL.Quit();
}
catch
{
MessageBox.Show("Something went wrong");
}
}
/// <summary>
/// Кнопка создания XLS и сохранения результатов
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button3_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel не установлен!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Кол-во студентов";
xlWorkSheet.Cells[1, 2] = "Кол-во отличников";
xlWorkSheet.Cells[1, 3] = "Процент отличников";
xlWorkSheet.Cells[2, 1] = stCap.ToString();
xlWorkSheet.Cells[2, 2] = otlCap.ToString();
xlWorkSheet.Cells[2, 3] = perc.ToString();
xlWorkBook.SaveAs("marks.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
MessageBox.Show("Файл marks.xls в папке проекта успешно создан!");
}
private static void ExportToExcel(DataGridView dtgvExport, string modelNumber)
{
// Creating a Excel object.
Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = modelNumber + "_Result";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (int i = 0; i < dtgvExport.Rows.Count - 1; i++)
{
for (int j = 0; j < dtgvExport.Columns.Count; j++)
{
// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dtgvExport.Columns[j].HeaderText;
}
else
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dtgvExport.Rows[i].Cells[j].Value.ToString();
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void readExcel_Click(object sender, EventArgs e)
{
RESET_Click(sender, e);
readExcel.BackColor = System.Drawing.Color.Chartreuse;
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Open("d:\\csharp-Excel.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Range aRange = xlWorkSheet.get_Range("A1", "A2");
if (aRange == null)
{
MessageBox.Show("Could not get a range. Check to be sure you have the correct versions of the office DLLs.");
}
else
{
MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString());
MessageBox.Show(xlWorkSheet.get_Range("A2", "A2").Value2.ToString());
}
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
/// <summary>
/// 表导出Excel
/// </summary>
/// <param name="modeFilePath">模板路径</param>
/// <param name="saveFilePath">保存文件路径</param>
/// <param name="tableName">导出数据名称</param>
public void ListToExcel(string modeFilePath, string saveFilePath, string tableName)
{
//需要添加 Microsoft.Office.Interop.Excel引用
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)
{
Console.Write("服务器上缺少Excel组件，需要安装Office软件。");
}
app.Visible = false;
app.UserControl = true;
Excel.Workbooks workbooks = app.Workbooks;
//加载模板
Excel._Workbook workbook = workbooks.Add(modeFilePath);
Excel.Sheets sheets = workbook.Sheets;
try
{
switch (tableName)
{
case "SingleInspection":
//SettlementObservationWriteData(workbook, sheets);
SingleInspectionWriteData(workbook, sheets,saveFilePath);
break;
case "LoftingScore":
LoftingScoreWriteData(workbook, sheets);
break;
//.........
}
//保存生成的Excel文件。
//Missing 在System.Reflection命名空间下。
workbook.SaveAs(saveFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//按顺序释放资源。
eb.NAR(sheets);
eb.NAR(workbook);
eb.NAR(workbooks);
app.Quit();
eb.NAR(app);
eb.KillProcess("EXCEL");//杀死excel进程
switch(tableName)
{
case "SingleInspection":
ef.Save_Excel(saveFilePath, "施工测量放样报验单");
break;
}
ef.Get_Excel(14, @"D:\123.xls");
}
catch (Exception ex)
{
MessageBox.Show("导出异常" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
eb.KillProcess("EXCEL");//杀死excel进程
}
}
public void Read(string filename)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(filename);
// Get worksheet names
foreach (Microsoft.Office.Interop.Excel.Worksheet sh in wb.Worksheets)
Debug.WriteLine(sh.Name);
// Get values from sheets SH1 and SH3 (in my file)
//object val1 = wb.Sheets["SH1"].Cells[1, "A"].Value2;
//object val3 = wb.Sheets["SH3"].Cells[1, "A"].Value2;
//Debug.WriteLine("{0} / {1}", val1, val3);
wb.Close();
excel.Quit();
}
/// <summary>
/// "Export to Excel" button, uses textbox to left to determine fileName
/// </summary>
private void btnCreateExcelFile_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
//Content Type
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 30;
Microsoft.Office.Interop.Excel.Workbook wb = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet wsContentType = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
// storing header part in Excel
for (int i = 1; i < grdReport.Columns.Count + 1; i++)
{
wsContentType.Cells[1, i] = grdReport.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < grdReport.Rows.Count - 1; i++)
{
for (int j = 0; j < grdReport.Columns.Count; j++)
{
wsContentType.Cells[i + 2, j + 1] = grdReport.Rows[i].Cells[j].Value.ToString();
}
}
if (saveContentType.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
ExcelApp.ActiveWorkbook.SaveCopyAs(saveContentType.FileName);
ExcelApp.ActiveWorkbook.Saved = true;
}
ExcelApp.Quit();
}
catch (Exception exp)
{
LogError(exp);
MessageBox.Show("Failed to create Excel file");
}
}
public void Convert(String metricsFolder)
{
metrics_files = Directory.GetFiles(metricsFolder);
foreach (String file in metrics_files)
{
string[] nameArray = file.Split('\\'); //get an array of all elements split at \\
name = nameArray[nameArray.Length - 1]; //return the last element of the array which shoudl always be the file name
string path = metricsFolder + "\\backups\\";
//check to make sure backup directory exists, otherwise create it
if (!Directory.Exists(path))
Directory.CreateDirectory(path);
var app = new Microsoft.Office.Interop.Excel.Application();
var wb = app.Workbooks.Open(file);
wb.SaveAs(Filename: file + "x", FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook);
wb.Close();
app.Quit();
path += name;//add the file name to the backup path so the move can be completed
File.Move(file, path);
}
}
public void createPortfolioXlFile(string folderPath)
{
if (!System.IO.Directory.Exists(folderPath))
{
throw new DirectoryNotFoundException();
}
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
object misValue = System.Reflection.Missing.Value;
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int count = 1;
foreach (string colName in columnNames)
{
xlWorkSheet.Cells[1, count++] = colName;
}
Excel.Range row1 = xlWorkSheet.UsedRange;
row1.Columns.AutoFit();
try
{
xlWorkBook.SaveAs(path);
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
}
finally
{
Utility.releaseObject(xlWorkSheet);
Utility.releaseObject(xlWorkBook);
Utility.releaseObject(xlApp);
}
}
private void drawGraph()
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook
while (thread1.IsAlive)
{
//son satır bulunuyor excel dosyasındaki
Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = xlWorkSheet.get_Range("A1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz
// alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor
string[] HeatingData = ReceiveData.Split(':');
string[] HeatingData2 = HeatingData[1].Split('D');
var result = HeatingData2[0];
double heating = Convert.ToDouble(result);
theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz!
string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss");
Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor!
this.Invoke((MethodInvoker)delegate
{
chart1.Series["Series1"].Points.AddY(result);
// excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor
xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100);
xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman;
});
}
}
catch
{
// MessageBox.Show("Dosya bulunamadı");
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Zaman";
xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius";
xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır");
}
}
/// <summary>
/// Exporta las tablas a un documento de Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Exportar_Excel(object sender, RoutedEventArgs e)
{
this.Cursor = Cursors.Wait;
//Fechas en el rango
string fechaInicialRango = dateInicio.Text.Replace("/", "-");
string fechaFinalRango = dateFinal.Text.Replace("/", "-");
//Documento de Excel
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkBook.Worksheets.Add();
xlWorkBook.Worksheets.Add();
//TABLA DE ENTREGAS
Excel.Worksheet xlWorkSheet;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = "Entregas";
for (int i = 0; i <= dataGridEntregas.Items.Count; i++)
{
for (int j = 0; j <= dataGridEntregas.Columns.Count - 1; j++)
{
//Se ponen los encabezados
if (i == 0)
{
switch (j + 1)
{
case 1: xlWorkSheet.Cells[i + 1, j + 1] = "FOLIO";
break;
case 2: xlWorkSheet.Cells[i + 1, j + 1] = "COSTO";
break;
case 3: xlWorkSheet.Cells[i + 1, j + 1] = "ABONO";
break;
case 4: xlWorkSheet.Cells[i + 1, j + 1] = "REALIZÓ";
break;
}
}
//Se pone la información
else
{
Entrada miEntrada = (Entrada)dataGridEntregas.Items[i - 1];
switch (j + 1)
{
case 1: xlWorkSheet.Cells[i + 1, j + 1] = miEntrada.folioNota.ToString();
break;
case 2: xlWorkSheet.Cells[i + 1, j + 1] = miEntrada.total.ToString(CultureInfo.InvariantCulture);
break;
case 3: xlWorkSheet.Cells[i + 1, j + 1] = miEntrada.anticipo.ToString(CultureInfo.InvariantCulture);
break;
case 4: xlWorkSheet.Cells[i + 1, j + 1] = miEntrada.hechaPor;
break;
}
}
}
}
xlWorkSheet.Cells[dataGridEntregas.Items.Count + 2, 3] = txtSubtotalEntregas.Text.Substring(11);
//TABLA DE SALIDAS
Excel.Worksheet xlWorkSheet2;
xlWorkSheet2 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
xlWorkSheet2.Name = "Salidas";
for (int i = 0; i <= dataGridSalidas.Items.Count; i++)
{
for (int j = 0; j <= dataGridSalidas.Columns.Count - 1; j++)
{
//Se ponen los encabezados
if (i == 0)
{
switch (j + 1)
{
case 1: xlWorkSheet2.Cells[i + 1, j + 1] = "DESCRIPCIÓN";
break;
case 2: xlWorkSheet2.Cells[i + 1, j + 1] = "COSTO";
break;
}
}
//Se pone la información
else
{
Salida miSalida = (Salida)dataGridSalidas.Items[i - 1];
switch (j + 1)
{
case 1: xlWorkSheet2.Cells[i + 1, j + 1] = miSalida.descripcion;
break;
case 2: xlWorkSheet2.Cells[i + 1, j + 1] = miSalida.costo.ToString(CultureInfo.InvariantCulture);
break;
}
}
}
}
xlWorkSheet2.Cells[dataGridSalidas.Items.Count + 2, 2] = txtSubtotalSalidas.Text.Substring(11);
//TOTAL
Excel.Worksheet xlWorkSheet4;
xlWorkSheet4 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3);
xlWorkSheet4.Name = "BALANCE";
xlWorkSheet4.Cells[1, 1] = "BALANCE";
xlWorkSheet4.Cells[1, 2] = txtBalance.Text;
xlWorkSheet4.Cells[3, 1] = "Consultado el en rango";
xlWorkSheet4.Cells[3, 2] = dateInicio.Text;
xlWorkSheet4.Cells[3, 3] = dateFinal.Text;
this.Cursor = Cursors.Arrow;
System.Windows.Forms.SaveFileDialog saveDlg = new System.Windows.Forms.SaveFileDialog();
saveDlg.FileName = "Balance "+fechaInicialRango+" "+fechaFinalRango+".xlsx";
saveDlg.InitialDirectory = @"C:\";
saveDlg.Filter = "Libros de Excel (*.xlsx)|*.xlsx";
saveDlg.FilterIndex = 0;
saveDlg.RestoreDirectory = true;
saveDlg.Title = "Indique dónde se guardará su libro de Excel | La Modistería";
if (saveDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string path = saveDlg.FileName;
xlWorkBook.SaveCopyAs(path);
xlWorkBook.Saved = true;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
MessageBox.Show("¡Su archivo de Excel se ha exportado exitosamente!");
}
public void Template(DataTable dt)
{
string serverpath = this.MapPath(".");
string date = System.DateTime.Now.ToString("ddMMyyyy_hhmmssm");
string newfile = @"" + serverpath + "\\ExcelReports\\BreakEven_Report_" + date + ".xls";
string filename = "BreakEven_Report_" + date + ".xls";
String ExcelPath = System.Configuration.ConfigurationManager.AppSettings["ExcelPath"].ToString();
string newpath = ExcelPath + "BreakEven_Report_" + date + ".xls";
//string newpath = "\\Reports\\ExcelReports\\BreakEven_Report_" + date + ".xls";
lnkexcel.HRef = newpath;
if (File.Exists(newfile) == false)
{
File.Copy(@"" + serverpath + "\\ExcelTemplates\\BreakEven_Report_Template.xls", newfile);
Microsoft.Office.Interop.Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlNewSheet = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
return;
xlWorkbook = xlApp.Workbooks.Open(newfile, 0, false, 5, "", "",
false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
xlSheets = xlWorkbook.Sheets as Excel.Sheets;
xlNewSheet = (Excel.Worksheet)xlSheets[2];
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
xlNewSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
xlNewSheet = (Excel.Worksheet)xlSheets[1];
hfBevnval.Value = string.IsNullOrEmpty(hfBevnval.Value) ? "0.0" : hfBevnval.Value;
xlNewSheet.Cells[14, 13] = hfBevnval.Value + "%";
//Excel.Worksheet excelc = null;
//excelc=(Excel.Worksheet)xlSheets[2];
//excelc.Cells.Value2("M14", "M14");
//xcel.Range oRange;
// Resize the columns
//oRange = xlNewSheet.get_Range(xlNewSheet.Cells[1, 1],
// xlNewSheet.Cells[rowCount, dt.Columns.Count]);
//oRange.EntireColumn.AutoFit();
objcf.HideDBData_XLSheets(xlSheets, "2");
xlWorkbook.Save();
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
if (proc.MainWindowTitle.ToString() == "")
{
//proc.Kill();
}
}
//Response.Redirect(newfilepath);
string fName = newfile;
//DownLoadFileFromServer(newpath);
}
finally
{
xlApp = null;
}
System.Web.UI.ScriptManager.RegisterStartupScript(this, this.GetType(), "", "anc();", true);
}
}
private void btnSendToExcel_Click(object sender, EventArgs e)
{
Excel.Application excel = null;
try
{
//DataGridView沒有資料就不執行
if (this.dgvReferOrderAll.Rows.Count <= 1)
{
MessageBox.Show("沒有可滙出的資料！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
string Date = DateTime.Now.ToString("yyyy-MM-dd");
//設定滙出後的存檔路徑(儲存在桌面)
string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) +
@"\BentoReport_" + Date + ".xls";
//new 出一個Excel
excel = new Microsoft.Office.Interop.Excel.Application();
//看的到Excel在工作
excel.Visible = false;
//新增加一工作簿
excel.Application.Workbooks.Add(true);
PGB pgb = new PGB();
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Maximum = dgvReferOrderAll.Rows.Count - 1;
pgb.progressBar1.Step = 1;
pgb.progressBar1.Value = 0;
pgb.progressBar1.ForeColor = Color.Pink;
pgb.progressBar1.Style = ProgressBarStyle.Continuous;
pgb.Show();
//寫入欄位名稱
for (int i = 0; i < dgvReferOrderAll.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dgvReferOrderAll.Columns[i].HeaderText;
}
//把DataGridView資料寫到Excel
for (int i = 0; i < dgvReferOrderAll.Rows.Count - 1; i++)
{
pgb.progressBar1.Value++;
for (int j = 0; j < dgvReferOrderAll.Columns.Count; j++)
{
if (dgvReferOrderAll[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgvReferOrderAll[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgvReferOrderAll[j, i].Value.ToString();
}
}
//設定欄位靠右
excel.get_Range("A" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.
xlHAlignRight;
excel.get_Range("C" + (i + 2)).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.
xlHAlignRight;
//設定欄位顏色
excel.get_Range("A" + (i + 2)).Interior.Color = Color.Pink;
excel.get_Range("B" + (i + 2)).Interior.Color = Color.Pink;
excel.get_Range("C" + (i + 2)).Interior.Color = Color.Pink;
excel.get_Range("D" + (i + 2)).Interior.Color = Color.Pink;
//設定欄位框線
excel.get_Range("A" + (i + 2)).Borders.LineStyle = 1;
excel.get_Range("B" + (i + 2)).Borders.LineStyle = 1;
excel.get_Range("C" + (i + 2)).Borders.LineStyle = 1;
excel.get_Range("D" + (i + 2)).Borders.LineStyle = 1;
}
/*
先將DataGridView的Rows總數給變數aa，以利後續透過aa+1的方式來新增要加入的資料
(將DataGridView資料轉至Excel後，在Rows下方插入TextBox.Text)
*/
int aa = dgvReferOrderAll.Rows.Count + 1;
excel.Cells[aa, 1] = "[葷]數量：";
excel.Cells[aa, 2] = txtTotalOrder0.Text;
excel.Cells[aa, 3] = "[素]數量：";
excel.Cells[aa, 4] = txtTotalOrder1.Text;
//設定欄位靠右
excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
//設定欄位顏色
excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
//設定欄位字體顏色
excel.get_Range("A" + aa).Font.Color = Color.Snow;
excel.get_Range("B" + aa).Font.Color = Color.Snow;
excel.get_Range("C" + aa).Font.Color = Color.Snow;
excel.get_Range("D" + aa).Font.Color = Color.Snow;
//設定欄位字體為粗體
excel.get_Range("A" + aa).Font.Bold = true;
excel.get_Range("B" + aa).Font.Bold = true;
excel.get_Range("C" + aa).Font.Bold = true;
excel.get_Range("D" + aa).Font.Bold = true;
aa = aa + 1;
excel.Cells[aa, 1] = "[葷]金額：";
excel.Cells[aa, 2] = txtTotalPrice0.Text;
excel.Cells[aa, 3] = "[素]金額：";
excel.Cells[aa, 4] = txtTotalPrice1.Text;
excel.get_Range("A" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("A" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("B" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("A" + aa).Font.Color = Color.Snow;
excel.get_Range("B" + aa).Font.Color = Color.Snow;
excel.get_Range("C" + aa).Font.Color = Color.Snow;
excel.get_Range("D" + aa).Font.Color = Color.Snow;
excel.get_Range("A" + aa).Font.Bold = true;
excel.get_Range("B" + aa).Font.Bold = true;
excel.get_Range("C" + aa).Font.Bold = true;
excel.get_Range("D" + aa).Font.Bold = true;
aa = aa + 1;
excel.Cells[aa, 3] = "總金額：";
excel.Cells[aa, 4] = txtTotalPriceAll.Text;
excel.get_Range("C" + aa).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
excel.get_Range("C" + aa).Interior.Color = Color.MediumOrchid;
excel.get_Range("D" + aa).Interior.Color = Color.MediumPurple;
excel.get_Range("C" + aa).Font.Color = Color.Snow;
excel.get_Range("D" + aa).Font.Color = Color.Snow;
excel.get_Range("C" + aa).Font.Bold = true;
excel.get_Range("D" + aa).Font.Bold = true;
//設定滙出後，欄位寛度自動配合資料調整
excel.Cells.EntireRow.AutoFit();
//自動調整列高
excel.Cells.EntireColumn.AutoFit();
//將所有欄位做垂直置中
excel.Cells.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
//將A1~D1的欄位做水平置中
excel.get_Range("A1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("B1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("C1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
excel.get_Range("D1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//設定A1~D1欄位框線
excel.get_Range("A1").Borders.LineStyle = 1;
excel.get_Range("B1").Borders.LineStyle = 1;
excel.get_Range("C1").Borders.LineStyle = 1;
excel.get_Range("D1").Borders.LineStyle = 1;
excel.get_Range("A1").Font.Color = Color.White;
excel.get_Range("A1").Interior.Color = Color.DodgerBlue;
excel.get_Range("B1").Font.Color = Color.White;
excel.get_Range("B1").Interior.Color = Color.DodgerBlue;
excel.get_Range("C1").Font.Color = Color.White;
excel.get_Range("C1").Interior.Color = Color.DodgerBlue;
excel.get_Range("D1").Font.Color = Color.White;
excel.get_Range("D1").Interior.Color = Color.DodgerBlue;
/*
excel.get_Range("E1").Font.Color = Color.White;
excel.get_Range("E1").Interior.Color = Color.DodgerBlue;
excel.get_Range("F1").Font.Color = Color.White;
excel.get_Range("F1").Interior.Color = Color.DodgerBlue;
excel.get_Range("G1").Font.Color = Color.White;
excel.get_Range("G1").Interior.Color = Color.DodgerBlue;
excel.get_Range("H1").Font.Color = Color.White;
excel.get_Range("H1").Interior.Color = Color.DodgerBlue;
excel.get_Range("I1").Font.Color = Color.White;
excel.get_Range("I1").Interior.Color = Color.DodgerBlue;
excel.get_Range("J1").Font.Color = Color.White;
excel.get_Range("J1").Interior.Color = Color.DodgerBlue;
excel.get_Range("K1").Font.Color = Color.White;
excel.get_Range("K1").Interior.Color = Color.DodgerBlue;
excel.get_Range("L1").Font.Color = Color.White;
excel.get_Range("L1").Interior.Color = Color.DodgerBlue;
excel.get_Range("M1").Font.Color = Color.White;
excel.get_Range("M1").Interior.Color = Color.DodgerBlue;
excel.get_Range("N1").Font.Color = Color.White;
excel.get_Range("N1").Interior.Color = Color.DodgerBlue;
excel.get_Range("O1").Font.Color = Color.White;
excel.get_Range("O1").Interior.Color = Color.DodgerBlue;
*/
//設置禁止彈出覆蓋或儲存的彈跳視窗
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
//將檔案儲存到SaveFile指定的位置
excel.ActiveWorkbook.SaveCopyAs(SaveFilePath);
pgb.Close();
MessageBox.Show("已成功滙出Excel檔！" + Environment.NewLine + "檔案儲存在您電腦的桌面，檔名：BentoReport_" +
Date + ".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//關閉工作簿和結束Excel程式
excel.Workbooks.Close();
excel.Quit();
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
}
public void Excelvalidation(string path, int sno, Excel.Sheets val_sheetlist)
{
#region Initial
Excel.Worksheet valsheet = (Excel.Worksheet)val_sheetlist[1];
FileInfo finfo = new FileInfo(path);
Microsoft.Office.Interop.Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlNewSheet = null;
Excel.Worksheet xlsummarysheet = null;
Excel.Worksheet xlscvsheet = null;
string ErrMsg = "", hidelist = "";
int i = 1;
try
{
valsheet.Cells[sno, 1] = finfo.Name;
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
return;
xlWorkbook = xlApp.Workbooks.Open(path, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
int boindex = 0, sumindex = 0, cvrindex = 0;
for (int l = 1; l <= xlWorkbook.Sheets.Count; l++)
{
if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "boxoffice")
{
boindex = l;
}
if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "coversheet")
{
cvrindex = l;
}
if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "summary")
{
sumindex = l;
}
}
xlSheets = xlWorkbook.Sheets as Excel.Sheets;
if (boindex != 0)
{
xlNewSheet = (Excel.Worksheet)xlSheets[boindex];
xlNewSheet.Unprotect("7135");
}
if (boindex != 0)
{
xlsummarysheet = (Excel.Worksheet)xlSheets[sumindex];
xlsummarysheet.Unprotect("7135");
}
#endregion
if (1 == valstatus)
{
hidelist = "2,3";
lnkvalexcel.Visible = true;
#region Validate process
if (xlNewSheet.ProtectContents)
{
ErrMsg = "File is protcted";
}
else if (boindex == 0 && sumindex == 0 && cvrindex == 0)
{
ErrMsg = "Sheet name not found!";
}
//if (!xlNewSheet.ProtectContents && (boindex != 0 && sumindex != 0 && cvrindex != 0))
if ((boindex != 0 && sumindex != 0 && cvrindex != 0))
{
string sch_day = "", sch_date = "", sch_time = "", tot_att = "", gross_sales = "", tot_exp = "", gross_rec = "",
Discount = "", SummaryMsg = "";
string royalty = "", gurantee = "", var_exp_act = "", share1 = "", share2 = "", share3 = "", iferr = "";
if (WriteError == 0)
{
#region Write Error
royalty = xlsummarysheet.Cells[31, 2].Text.Trim();
gurantee = xlsummarysheet.Cells[34, 2].Text.Trim();
var_exp_act = xlsummarysheet.Cells[54, 2].Text.Trim();
share1 = xlsummarysheet.Cells[90, 2].Text.Trim();
share2 = xlsummarysheet.Cells[93, 2].Text.Trim();
share3 = xlsummarysheet.Cells[96, 2].Text.Trim();
if (royalty.AutoformatDecimal() == 0)
SummaryMsg += "Royalty Zero,";
if (gurantee.AutoformatDecimal() == 0)
SummaryMsg += "Guarantee Zero,";
if (var_exp_act.AutoformatDecimal() == 0)
SummaryMsg += "Expenses Zero,";
//if (share1.AutoformatDecimal() + share2.AutoformatDecimal() + share3.AutoformatDecimal() != 100)
// SummaryMsg += "Total share Not equal to 100,";
iferr = "";
for (i = 3; i < 13; i++)
{
ErrMsg = ""; sch_day = ""; sch_date = ""; sch_time = ""; tot_att = ""; gross_sales = ""; tot_exp = "0"; gross_rec = "";
sch_date = (xlNewSheet.Cells[1, i].Value != null) ? Convert.ToString(xlNewSheet.Cells[1, i].Value).Trim() : sch_date;
sch_day = Convert.ToString(xlNewSheet.Cells[2, i].Text).ToString().Trim();
sch_time = Convert.ToString(xlNewSheet.Cells[3, i].Text).ToString().Trim();
tot_att = Convert.ToString(xlNewSheet.Cells[9, i].Value).ToString().Trim();
gross_sales = Convert.ToString(xlNewSheet.Cells[13, i].Value).ToString().Trim();
//tot_exp = Convert.ToString(xlNewSheet.Cells[26, i].Value).ToString().Trim();
gross_rec = (xlNewSheet.Cells[31, i].Value != null) ? Convert.ToString(xlNewSheet.Cells[31, i].Value.ToString()).Trim() : gross_rec;
sch_date = (sch_date.ToLower().Contains("perf") == true) ? "0" : "1";
sch_day = (sch_day.Contains("Day") == true) ? "0" : "1";
sch_time = (sch_time.Contains("time") == true) ? "0" : "1";
tot_att = (tot_att.AutoformatInt() == 0) ? "0" : "1";
gross_sales = (gross_sales.AutoformatDecimal() == 0) ? "0" : "1";
//tot_exp = (tot_exp.AutoformatDecimal() == 0) ? "0" : "1";
gross_rec = (gross_rec.AutoformatDecimal() == 0) ? "0" : "1";
string ticket = "", price = "", ps_tkt_price = "";
for (int j = 100; j <= 110; j++)
{
ticket = xlNewSheet.Cells[j, 1].Text.Trim();
price = xlNewSheet.Cells[j, i].Text.Trim();
price = (price.AutoformatDecimal() == null) ? "0" : price.AutoformatDecimal().ToString();
if ((ticket.Contains("#") == true && price.AutoformatDecimal() > 0) || (ticket != "" && ticket.Contains("#") == false && price.AutoformatDecimal() == 0))
{
ps_tkt_price = "0";
}
}
int totcnt = Convert.ToInt32(sch_date.AutoformatInt() + sch_day.AutoformatInt() + sch_time.AutoformatInt() + tot_att.AutoformatInt() +
gross_sales.AutoformatInt() + tot_exp.AutoformatInt() + gross_rec.AutoformatInt());
if (totcnt < 6 && totcnt != 0)
{
if (sch_date.AutoformatInt() == 0)
{
//ErrMsg += "Schedule date error,";
}
if (sch_day.AutoformatInt() == 0)
{
ErrMsg += "Schedule day error,";
}
if (tot_att.AutoformatInt() == 0)
{
ErrMsg += "Total attendance error,";
}
if (gross_sales.AutoformatInt() == 0)
{
ErrMsg += "Gross sales error,";
}
//if (tot_exp.AutoformatInt() == 0)
//{
// ErrMsg += "Total expenses error,";
//}
if (gross_rec.AutoformatInt() == 0)
{
ErrMsg += "Gross receipts error,";
}
}
if (Discount.AutoformatInt() == 0 && i == 3)
{
ErrMsg += "Discount extra columns,";
}
if (ps_tkt_price.AutoformatInt() == 0 && totcnt == 7)
{
ErrMsg += "Ticket price mismatching,";
}
valsheet.Cells[sno, i] = ErrMsg;
if (string.IsNullOrEmpty(ErrMsg) == false && string.IsNullOrEmpty(iferr) == true)
iferr = ErrMsg;
}
valsheet.Cells[sno, 13] = SummaryMsg;
#endregion
}
xlscvsheet = (Excel.Worksheet)xlSheets[cvrindex];
string recid = "";
#region GetMySQLRecID
string shname = "", city = "", venue = "", presenter = "";
DateTime opdate, endate;
shname = xlscvsheet.Cells[1, 1].Text;
city = xlscvsheet.Cells[3, 2].Text;
venue = xlscvsheet.Cells[4, 2].Text;
presenter = xlscvsheet.Cells[5, 2].Text;
if (!string.IsNullOrEmpty(xlscvsheet.Cells[8, 1].Text))
{
opdate = Convert.ToDateTime(xlscvsheet.Cells[8, 1].Text);
endate = Convert.ToDateTime(xlscvsheet.Cells[8, 2].Text);
valsheet.Cells[sno, 14] = shname;
valsheet.Cells[sno, 15] = city;
valsheet.Cells[sno, 16] = opdate;
valsheet.Cells[sno, 17] = endate;
if (AllNew != 0)
{
MasterDataLayer.MasterData objmst = new MasterDataLayer.MasterData();
DataTable dt = new DataTable();
dt = objmst.GetMysql_Recordid(shname, city, opdate, endate);
if (dt.Rows.Count > 0)
{
recid = dt.Rows[0]["RecordID"].ToString();
}
}
}
else
{
recid = "Opening date not found!";
}
valsheet.Cells[sno, 2] = recid;
#endregion
if (!string.IsNullOrEmpty(ErrMsg) || !string.IsNullOrEmpty(SummaryMsg))
{
hdnvalidation_status.Value = "1";
}
else if (recid.ToLower().Contains("new") == false && string.IsNullOrEmpty(iferr) == true)
{
//xlWorkbook.SaveAs(Saveaspath + finfo.Name);
valsheet.Cells[sno, 25] = "Pass";
}
else { valsheet.Cells[sno, 25] = "Fail"; }
}
else
{
valsheet.Cells[sno, i + 1] = ErrMsg;
}
#endregion
}
else
{
lnkexcelclms.Visible = true;
hidelist = "1";
write_excel_columns(finfo, xlNewSheet, xlsummarysheet, sno, val_sheetlist);
}
// objcf.HideDBData_XLSheets(val_sheetlist, "1");
// xlWorkbook.Save();
xlWorkbook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
}
catch (Exception ex)
{
valsheet.Cells[sno, i + 1] = ex.Message + " File Name: " + finfo.Name;
xlWorkbook.Save();
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
// throw new Exception(ex.Message + " File Name: " + finfo.Name);
}
finally
{
// objcf.HideDBData_XLSheets(val_sheetlist, hidelist);
xlApp = null;
}
}
public void write_to_db()
{
int sno = 2;
Kill_Excel_Process();
string valexcelpath = ValidationInputPath;
Microsoft.Office.Interop.Excel.Application xlApp1 = null;
Excel.Workbook xlWorkbook1 = null;
Excel.Sheets val_sheets = null;
Excel.Worksheet valsheet = null;
Excel.Worksheet xlClmWritingsheet = null;
xlApp1 = new Microsoft.Office.Interop.Excel.Application();
xlWorkbook1 = xlApp1.Workbooks.Open(valexcelpath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
val_sheets = xlWorkbook1.Sheets as Excel.Sheets;
valsheet = (Excel.Worksheet)val_sheets[1];
xlClmWritingsheet = (Excel.Worksheet)val_sheets[2];
string file, _recoid, _psWeek, _cityid = "0";
try
{
for (int e = 2; e < 5000; e++)
{
try
{
file = valsheet.Cells[e, 1].Text;
_recoid = valsheet.Cells[e, 2].Text;
_psWeek = valsheet.Cells[e, 23].Text;
if (_recoid.ToLower() == "new")
{
_recoid = createnewengt(e, valsheet);
_cityid = _recoid.ToString().Split(',').GetValue(1).ToString();
_recoid = _recoid.ToString().Split(',').GetValue(0).ToString();
valsheet.Cells[e, 2] = _recoid;
valsheet.Cells[e, 20] = _cityid;
}
else
{
_cityid = valsheet.Cells[e, 20].Text;
}
if (string.IsNullOrEmpty(file) == true)
break;
file = ExcelFolderPath + file;
if (_cityid != "0" && _recoid != "0")
{
Read(file, sno, _recoid, _psWeek);
valsheet.Cells[e, 25] = "Pass";
}
}
catch (Exception ex1)
{
valsheet.Cells[e, 25] = "Fail: " + ex1.Message;
}
sno++;
}
}
finally
{
xlWorkbook1.Save();
xlWorkbook1.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp1.Quit();
GC.Collect();
Kill_Excel_Process();
}
}
private void 导出ExcelToolStripMenuItem1_Click(object sender, EventArgs e)
{
string fileName;
saveFileDialog1.Filter = "Excel 2003 *.xls|*.xls|Excel 2007 *.xlsx|*.xlsx";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
fileName = saveFileDialog1.FileName;
Excel.Application app =
new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Workbook wBook = app.Workbooks.Add(true);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
DsToWsheet(ds, wSheet);
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
wBook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
app.Quit();
app = null;
}
}
//生成Excel
private bool ExportToExcel(DataTable dt)
{
bool result = false;
this.UseWaitCursor = true;
if (dt.Rows.Count > 65536)
{
MessageBox.Show("數據記錄太多(最多不能超過65536條)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return result;
}
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
labMsg.Text = "Excel正在匯出中…………";
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的机器未安装Excel");
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(true);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
worksheet.Name = "過膠機生產日報表";
//worksheet.QueryTables.Add
#region worksheet的格式
worksheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4; //設置頁面A4打印
worksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape; //設置橫向打印
worksheet.PageSetup.Zoom = false; //設置頁面縮放比例 Zoom必須設為False FitToPagesWide才有效
worksheet.PageSetup.FitToPagesWide = 1;//設置葉寬為一頁
worksheet.PageSetup.CenterHorizontally = true;//頁面水平居中
worksheet.PageSetup.TopMargin = xlApp.InchesToPoints(0.275590551181102);
worksheet.PageSetup.BottomMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.LeftMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.RightMargin = xlApp.InchesToPoints(0.196850393700787);
worksheet.PageSetup.FooterMargin = xlApp.InchesToPoints(0.31496062992126);
worksheet.PageSetup.HeaderMargin = xlApp.InchesToPoints(0.31496062992126);
xlApp.Visible = false;
#endregion
#region 列名
#region 循環填充列名，很慢，已經REMARK
//for (i = 1; i <= dt.Columns.Count - 1; i++)
//{
// if (i == 11 || i == 12 || i == 13)
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Substring(8, 3);
// }
// else if (i == 14 || i == 15 || i == 16 || i == 17)
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim().Substring(8);
// }
// else if (i >= 23 && i <= 31)
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex + 1, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex + 1, i] = dt.Columns[i].ColumnName.Trim();
// }
// else
// {
// MergeColumn(xlApp, xlApp.Cells[RowIndex, i], xlApp.Cells[RowIndex + 1, i], "", 12, 65535);
// xlApp.Cells[RowIndex, i] = dt.Columns[i].ColumnName;
// }
//}
#endregion
int RowIndex = 2;
MergeColumn(xlApp, xlApp.Cells[1, 1], xlApp.Cells[1, 30], "過膠機生產日報表", 16, 65535, 0); //大標題
MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 13], "當更生產數量", 12, 65535, 0);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 17], "過膠行車長度", 12, 65535, 0);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 30], "常用物料使用情況", 12, 65535, 0);
RowIndex = RowIndex + 1;
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 1], xlApp.Cells[RowIndex, 1], "生產日期", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 2], xlApp.Cells[RowIndex, 2], "班次", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 3], xlApp.Cells[RowIndex, 3], "機台編號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 4], xlApp.Cells[RowIndex, 4], "機組人員", 12, 65535, 21.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 5], xlApp.Cells[RowIndex, 5], "工程單號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 6], xlApp.Cells[RowIndex, 6], "產品名稱", 12, 65535, 15);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 7], xlApp.Cells[RowIndex, 7], "印張編號", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 8], xlApp.Cells[RowIndex, 8], "生產內容", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 9], xlApp.Cells[RowIndex, 9], "版本", 12, 65535, 7.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 10], xlApp.Cells[RowIndex, 10], "訂單數量", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 11], xlApp.Cells[RowIndex, 11], "正品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 12], xlApp.Cells[RowIndex, 12], "次品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 13], xlApp.Cells[RowIndex, 13], "廢品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 14], xlApp.Cells[RowIndex, 14], "單 張(cm)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 15], xlApp.Cells[RowIndex, 15], "正品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 16], xlApp.Cells[RowIndex, 16], "次品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 17], xlApp.Cells[RowIndex, 17], "廢品數(m)", 12, 65535, 10);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 18], xlApp.Cells[RowIndex, 18], "上工序次品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 19], xlApp.Cells[RowIndex, 19], "上工序廢品數", 12, 65535, 7);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 20], xlApp.Cells[RowIndex, 20], "累計數量", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 21], xlApp.Cells[RowIndex, 21], "級別", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex - 1, 22], xlApp.Cells[RowIndex, 22], "工程狀態", 12, 65535, 9.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 23], xlApp.Cells[RowIndex, 23], "物料編號", 12, 65535, 12);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 24], xlApp.Cells[RowIndex, 24], "物料名稱", 12, 65535, 35);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 25], xlApp.Cells[RowIndex, 25], "採購單號", 12, 65535, 12);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 26], xlApp.Cells[RowIndex, 26], "生產批次", 12, 65535, 12);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 27], xlApp.Cells[RowIndex, 27], "實際用量", 12, 65535, 8.5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 28], xlApp.Cells[RowIndex, 28], "單位", 12, 65535, 5);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 29], xlApp.Cells[RowIndex, 29], "寬度(mm)", 12, 65535, 9);
MergeColumn(xlApp, xlApp.Cells[RowIndex, 30], xlApp.Cells[RowIndex, 30], "長度(m)", 12, 65535, 9);
#endregion
#region 填充數據 向Excel中逐行逐列写入表格中的数据
RowIndex = RowIndex + 1; //數據開始的行號
int ExcelCurrectRow = RowIndex; //當前行
int dtRowNO;
int realRowNO = 0;//dt實際行
int dtKeyRowNO = RowIndex;//NO:4
int megreCount = 0;
int dtMetrialRowNO = 23;//物料編號所在列
int tempRow;
int tempCol;
#region 較慢的填充數據方法 已經REMARK
//for (int row = 0; row < dt.Rows.Count; row++)
//{
// //DataRow[] rows = dt.Select(dt.Rows[row][0].ToString());
// for (int col = 1; col < dt.Columns.Count; col++)
// {
// if (row > 0) //班次內工程ID相等 col22之後不合併
// {
// if (dt.Rows[row - 1][0].ToString() == dt.Rows[row][0].ToString() && col < 22)
// {
// CellStyle(xlApp, xlApp.Cells[currectRow, col], xlApp.Cells[RowIndex, col], 10, -4142, true);
// }
// else
// {
// CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false);
// xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString();
// if (col < 22) currectRow = RowIndex;
// }
// }
// else
// {
// CellStyle(xlApp, xlApp.Cells[RowIndex, col], xlApp.Cells[RowIndex, col], 10, -4142, false);
// xlApp.Cells[RowIndex, col] = dt.Rows[row][col].ToString();
// }
// }
// RowIndex++;
// //if (RowIndex == 15) goto range; // for 測試
//}
#endregion
DataTable tbPJSFID = dt.DefaultView.ToTable(true, new string[] { "GGuID" });
for (dtRowNO = 0; dtRowNO < tbPJSFID.Rows.Count; dtRowNO++)
{
DataRow[] currectKeyRow = dt.Select(" GGuID = '" + dt.Rows[realRowNO][0].ToString() + "' ");
megreCount = currectKeyRow.Length;
for (tempRow = 0; tempRow < megreCount; tempRow++)
{
if (tempRow == 0)
{
for (tempCol = 1; tempCol < dtMetrialRowNO; tempCol++) //先合併物料編號之前的單元格
{
CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO + megreCount - 1, tempCol], currectKeyRow[tempRow][tempCol].ToString(), true);
}
realRowNO += megreCount;
}
for (tempCol = dtMetrialRowNO; tempCol < dt.Columns.Count; tempCol++) //然後再填充物料
{
CellStyle(xlApp, xlApp.Cells[dtKeyRowNO, tempCol], xlApp.Cells[dtKeyRowNO, tempCol], currectKeyRow[tempRow][tempCol].ToString(), false);
}
dtKeyRowNO++;
}
//dtKeyRowNO++;
//if (realRowNO > dt.Rows.Count) break;
/*
* DataTable pjsf_iPJSFID = dt.DefaultView.ToTable(true, new string[] { "pjsf_iPJSFID" });
List<Thread> threads = new List<Thread>();
for (dtRowNO = 0; dtRowNO < pjsf_iPJSFID.Rows.Count; dtRowNO++)
{
*/
}
#endregion
#region 設置Sheet邊框
//range:
Excel.Range SheetRangeMargin = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count - 1]);
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
SheetRangeMargin.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
#endregion
Excel.Range DataFormat = xlApp.get_Range(xlApp.Cells[3, 1], xlApp.Cells[dt.Rows.Count + 3, dt.Columns.Count]);
DataFormat.Font.Name = "楷体";
DataFormat.Font.Size = 10;
Excel.Range IntFormat = xlApp.get_Range(xlApp.Cells[3, 15], xlApp.Cells[dt.Rows.Count + 3, 17]);
IntFormat.NumberFormatLocal = "0.00";
#region savecode
Object missing = Missing.Value;
SaveFileDialog saveForm = new SaveFileDialog();
saveForm.Filter = "Execl files (*.xls)|*.xls";
saveForm.FilterIndex = 0;
saveForm.FileName = "過膠機生產日報表";
if (saveForm.ShowDialog() == DialogResult.OK)
{
string fileName = saveForm.FileName;
workbook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
}
#endregion
result = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
labMsg.Text = "";
this.UseWaitCursor = false;
xlApp.Quit();
IntPtr t = new IntPtr(xlApp.Hwnd); //杀死进程的好方法
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
System.GC.Collect();
}
labMsg.Text = "";
return result;
}
private void MainMenuSaveAs_Click(object sender, EventArgs e)
{
if (flag_open)
{
// MainFormSaveFileDialog.FileName = _dataFile;
string filter = "Книга Microsoft Office Excel (*.xls)|*.xls";
// string filter = "CSV(разделители запятые) (*.csv)|*.csv";
MainFormSaveFileDialog.Filter = filter;
//MainFormSaveFileDialog.DefaultExt = "csv";
MainFormSaveFileDialog.DefaultExt = "xls";
if (MainFormSaveFileDialog.ShowDialog() == DialogResult.OK)
{
int i;
//System.IO.File.WriteAllLines(MainFormSaveFileDialog.FileName, list_out);
//bool _result = System.IO.File.Exists(MainFormSaveFileDialog.FileName);
if (System.IO.File.Exists(MainFormSaveFileDialog.FileName))
System.IO.File.Delete(MainFormSaveFileDialog.FileName);
if (MainFormCheckBoxResults.Checked == true)
{
flag_error = false;
CheckOutData(MainFormSaveFileDialog.FileName);
}
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
object opt = Type.Missing;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook ExcelAppWorkbook = (Microsoft.Office.Interop.Excel.Workbook)ExcelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet ExcelWorksheet = ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveSheet);
info.NumberDecimalSeparator = ".";
if (MainFormFactoryUnitIDTextBox.Text != "")
for (i = 0; i < list_out_height.Count; i++)
ExcelWorksheet.Cells[i + 1, 1] = MainFormFactoryUnitIDTextBox.Text;
if (MainFormCalibTypeIDTextBox.Text != "")
for (i = 0; i < list_out_height.Count; i++)
ExcelWorksheet.Cells[i + 1, 2] = MainFormCalibTypeIDTextBox.Text;
/*
if (list_out_kvant.Count == list_out_height.Count)
for (i = 0; i < list_out_kvant.Count; i++)
ExcelWorksheet.Cells[i + 1, 5] = list_out_kvant[i];
*/
/* if (Convert.ToDouble(list_out_kvant.Count/list_out_height.Count,info) < Convert.ToDouble(0.11,info))
for (i = 0; i < list_out_kvant.Count*10; i+=10)
ExcelWorksheet.Cells[i + 1, 5] = list_out_kvant[i/10];
else
for (i = 0; i < list_out_kvant.Count; i++)
ExcelWorksheet.Cells[i + 1, 5] = list_out_kvant[i];
*/
for (i = 0; i < list_out_height.Count; i++)
{
ExcelWorksheet.Cells[i + 1, 3] = Convert.ToDouble(list_out_height[i],info);
ExcelWorksheet.Cells[i + 1, 4] = Convert.ToDouble(list_out_volume[i],info);
}
ExcelAppWorkbook.SaveAs(MainFormSaveFileDialog.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7);
// ExcelApp.Visible = true;
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
ExcelAppWorkbook.Close(null, null, null);
ExcelApp.Quit();
Marshal.ReleaseComObject(ExcelAppWorkbook);
Marshal.ReleaseComObject(ExcelApp);
ExcelApp = null;
GC.Collect();
MainFormLabelStatusBar.Text = "Файл сохранен";
if(!flag_error)
MainFormLabelResult.Text = "Забирай свои калибровки...";
else
MainFormLabelResult.Text = "Забирай свои калибровки...и проверь файл с ошибками:" + _errorFileName;
this.AutoSize = true;
this.StartPosition = FormStartPosition.CenterScreen;
}
}
else
MessageBox.Show("Чтобы что-то сохранить, нужно сначала что-то открыть...", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
public void Template(DataSet ds)
{
DataTable dt = new DataTable();
dt = ds.Tables[0];
string serverpath = this.MapPath(".");
//string filename = "Settlement_Reports_" + System.DateTime.Now.ToString("ddmmyyyy_hh_mm_ss") + ".xls";
string filename = "Settlement_Reports_" + System.DateTime.Now.ToString("ddMMyyyy_hhmmss") + ".xlsx";
//DeleteCreatedFiles(filename, @"" + serverpath + "\\ExcelReports\\");
string newfile = @"" + serverpath + "\\Reports\\ExcelReports\\" + filename;
String ExcelPath = System.Configuration.ConfigurationManager.AppSettings["ExcelPath"].ToString();
string newpath = ExcelPath + filename;
//string newpath = "\\Reports\\ExcelReports\\" + filename;
if (File.Exists(newfile) == false)
{
File.Copy(@"" + serverpath + "\\Reports\\ExcelTemplates\\Settlement_Reports_Template.xlsx", newfile);
Microsoft.Office.Interop.Excel.Application xlApp = null;
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = null;
Microsoft.Office.Interop.Excel.Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.Worksheet xlNewSheet = null;
Excel.Worksheet xlCoverSheet = null;
try
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
return;
xlWorkbook = xlApp.Workbooks.Open(newfile, 0, false, 5, "", "",
false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
xlSheets = xlWorkbook.Sheets as Excel.Sheets;
xlNewSheet = (Excel.Worksheet)xlSheets[5];
xlCoverSheet = (Excel.Worksheet)xlSheets[1];
int rowCount = 2;
int colstart = dt.Columns["Schedule_Type"].Ordinal;
int i;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (i = 1; i < dt.Columns.Count + 1; i++)
{
xlNewSheet.Cells[rowCount, (i + 1)] = dr[i - 1].ToString();
}
break;
}
int k = 0;
foreach (DataRow dr in dt.Rows)
{
if (k == 0)
{
k++;
continue;
}
rowCount += 1;
for (i = colstart; i <= (dt.Columns.Count); i++)
{
xlNewSheet.Cells[rowCount, (i + 1)] = dr[i - 1].ToString();
}
}
rowCount = 17;
if (ds.Tables[1].Rows.Count > 0)
{
xlNewSheet.Cells[rowCount, (2)] = ds.Tables[1].Rows[0]["TOTALGROSSPERWK"].ToString();
xlNewSheet.Cells[rowCount, (3)] = ds.Tables[1].Rows[0]["SUM_PER_PS_SCALE"].ToString();
}
//objcf.HideDBData_XLSheets(xlSheets, "5");
if (ds.Tables[2].Rows.Count > 0)
{
write_coversheet_data(xlCoverSheet, ds.Tables[2]);
}
xlWorkbook.Save();
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
foreach (System.Diagnostics.Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
if (proc.MainWindowTitle.ToString() == "")
{
// proc.Kill();
}
}
//Response.Redirect(newfilepath);
string fName = newfile;
hdnfilepath.Value = newpath;
lnkexcel.HRef = newpath;
Image2.Visible = true;
}
finally
{
xlApp = null;
}
//ScriptManager.RegisterStartupScript(this, this.GetType(), "", "anc();", true);
}
}
public static void createReport()
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Range chartRange;
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Columns.AutoFit();
xlWorkSheet.get_Range("A1", "C1").Merge(false);
xlWorkSheet.get_Range("A1", "C1").Cells.Font.Size = 16;
xlWorkSheet.get_Range("A1").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("A1").Cells.FormulaR1C1 = "TRƯỜNG ĐH CÔNG NGHỆ THÔNG TIN";
xlWorkSheet.Cells[1, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.get_Range("A2", "C2").Merge(false);
xlWorkSheet.get_Range("A2", "C2").Cells.Font.Size = 16;
xlWorkSheet.get_Range("A2").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("A2").Cells.Font.Bold = true;
xlWorkSheet.get_Range("A2").Cells.FormulaR1C1 = "PHÒNG ĐÀO TẠO ĐẠI HỌC";
xlWorkSheet.Cells[2, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.get_Range("A4", "E4").Merge(false);
xlWorkSheet.get_Range("A4", "E4").Cells.Font.Size = 20;
xlWorkSheet.get_Range("A4").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("A4").Cells.Font.Bold = true;
xlWorkSheet.get_Range("A4").Cells.FormulaR1C1 = "BẢNG ĐIỂM CUỐI KỲ";
xlWorkSheet.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.get_Range("A5", "C5").Merge(false);
xlWorkSheet.get_Range("A5", "C5").Cells.Font.Size = 16;
xlWorkSheet.get_Range("A5").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("A5").Cells.Font.Bold = true;
xlWorkSheet.get_Range("A5").Cells.FormulaR1C1 = "HỌC KỲ: ";
xlWorkSheet.get_Range("A5").Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
xlWorkSheet.get_Range("D5", "E5").Merge(false);
xlWorkSheet.get_Range("D5", "E5").Cells.Font.Size = 16;
xlWorkSheet.get_Range("D5").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("D5").Cells.Font.Bold = true;
xlWorkSheet.get_Range("D5").Cells.FormulaR1C1 = "NĂM HỌC: ";
xlWorkSheet.get_Range("F5", "G5").Merge(false);
xlWorkSheet.get_Range("F5", "G5").Cells.Font.Size = 16;
xlWorkSheet.get_Range("F5").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("F5").Cells.Font.Bold = true;
xlWorkSheet.get_Range("F5").Cells.FormulaR1C1 = "Trọng số: ";
xlWorkSheet.get_Range("A6", "C6").Merge(false);
xlWorkSheet.get_Range("A6", "C6").Cells.Font.Size = 16;
xlWorkSheet.get_Range("A6").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("A6").Cells.Font.Bold = true;
xlWorkSheet.get_Range("A6").Cells.FormulaR1C1 = "Môn học: ";
xlWorkSheet.get_Range("D6", "E6").Merge(false);
xlWorkSheet.get_Range("D6", "E6").Cells.Font.Size = 16;
xlWorkSheet.get_Range("D6").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("D6").Cells.Font.Bold = true;
xlWorkSheet.get_Range("D6").Cells.FormulaR1C1 = "Lớp: ";
xlWorkSheet.get_Range("F6", "G6").Merge(false);
xlWorkSheet.get_Range("F6", "G6").Cells.Font.Size = 16;
xlWorkSheet.get_Range("F6").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("F6").Cells.Font.Bold = true;
xlWorkSheet.get_Range("F6").Cells.FormulaR1C1 = "Ngày thi: ";
xlWorkSheet.get_Range("A7", "C7").Merge(false);
xlWorkSheet.get_Range("A7", "C7").Cells.Font.Size = 16;
xlWorkSheet.get_Range("A7").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("A7").Cells.Font.Bold = true;
xlWorkSheet.get_Range("A7").Cells.FormulaR1C1 = "Giảng viên: ";
xlWorkSheet.get_Range("D7", "E7").Merge(false);
xlWorkSheet.get_Range("D7", "E7").Cells.Font.Size = 16;
xlWorkSheet.get_Range("D7").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("D7").Cells.Font.Bold = true;
xlWorkSheet.get_Range("D7").Cells.FormulaR1C1 = "Mã giảng viên: ";
xlWorkSheet.get_Range("F7", "G7").Merge(false);
xlWorkSheet.get_Range("F7", "G7").Cells.Font.Size = 16;
xlWorkSheet.get_Range("F7").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("F7").Cells.Font.Bold = true;
xlWorkSheet.get_Range("F7").Cells.FormulaR1C1 = "Phòng thi: ";
//Mark table
xlWorkSheet.get_Range("A10", "F10").Cells.Font.Size = 16;
xlWorkSheet.get_Range("A10", "F10").Cells.Font.Name = "Times New Roman";
xlWorkSheet.get_Range("A10", "F10").Cells.Font.Bold = true;
xlWorkSheet.get_Range("A10").Cells.EntireColumn.ColumnWidth = 8;
xlWorkSheet.get_Range("B10").Cells.EntireColumn.ColumnWidth = 30;
xlWorkSheet.get_Range("C10").Cells.EntireColumn.ColumnWidth = 45;
xlWorkSheet.get_Range("D10").Cells.EntireColumn.ColumnWidth = 15;
xlWorkSheet.get_Range("E10").Cells.EntireColumn.ColumnWidth = 25;
xlWorkSheet.get_Range("F10").Cells.EntireColumn.ColumnWidth = 20;
xlWorkSheet.get_Range("A10", "F10").Cells.EntireColumn.AutoFit();
xlWorkSheet.get_Range("A10", "F10").Cells.EntireRow.AutoFit();
xlWorkSheet.Cells[10, 1] = "STT";
xlWorkSheet.Cells[10, 2] = "MSSV";
xlWorkSheet.Cells[10, 3] = "Họ tên sinh viên";
xlWorkSheet.Cells[10, 4] = "Điểm số";
xlWorkSheet.Cells[10, 5] = "Điểm chữ";
xlWorkSheet.Cells[10, 6] = "Ghi chú";
xlWorkSheet.get_Range("A11", "F11").Cells.Font.Size = 14;
xlWorkSheet.get_Range("A11", "F11").Cells.Font.Name = "Times New Roman";
//Read data file here
xlWorkSheet.Cells[11, 1] = "1";
xlWorkSheet.Cells[11, 2] = "12520001";
xlWorkSheet.Cells[11, 3] = "Nguyễn Tuấn Anh";
xlWorkSheet.Cells[11, 4] = "9.0";
xlWorkSheet.Cells[11, 5] = "Chín";
xlWorkSheet.Cells[11, 6] = "";
try
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel file(*.xls)|*.xls";
sfd.FilterIndex = 1;
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; }
xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
MessageBox.Show("Excel file created success");
}
catch (Exception ex)
{
MessageBox.Show("Excel file created fail");
}
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public void validate(string from)
{
if (File.Exists(ValidationExcelPath) == true)
{
File.Delete(ValidationExcelPath);
}
File.Copy(ValidationExcelTemplate, ValidationExcelPath);
int sno = 2;
Kill_Excel_Process();
string valexcelpath = ValidationExcelPath;
Microsoft.Office.Interop.Excel.Application xlApp1 = null;
Excel.Workbook xlWorkbook1 = null;
Excel.Sheets val_sheets = null;
Excel.Worksheet valsheet = null;
Excel.Worksheet xlClmWritingsheet = null;
xlApp1 = new Microsoft.Office.Interop.Excel.Application();
xlWorkbook1 = xlApp1.Workbooks.Open(valexcelpath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
val_sheets = xlWorkbook1.Sheets as Excel.Sheets;
valsheet = (Excel.Worksheet)val_sheets[1];
xlClmWritingsheet = (Excel.Worksheet)val_sheets[2];
try
{
if (from == "folder")
{
foreach (string f in Directory.GetFiles(ExcelFolderPath))
{
if (f.ToLower().Contains("xls"))
{
Excelvalidation(f, sno, val_sheets);
// Read(f, sno);
sno++;
}
}
}
if (from == "excel")
{
string file, _recoid;
for (int e = 2; e < 5000; e++)
{
file = ExcelFolderPath + valsheet.Cells[e, 1].Text;
_recoid = valsheet.Cells[e, 2].Text;
if (_recoid.ToLower() == "new")
{
createnewengt(e, valsheet);
}
if (string.IsNullOrEmpty(file) == true)
break;
Excelvalidation(file, sno, val_sheets);
sno++;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + " S.No." + sno);
}
finally
{
xlWorkbook1.Save();
xlWorkbook1.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp1.Quit();
GC.Collect();
Kill_Excel_Process();
}
}
private void ExportDataSetToExcel(DataTable dt,DataTable dt2)
{
// Load Excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Create empty workbook
excel.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
Microsoft.Office.Interop.Excel.Worksheet workSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)excel.Sheets.Add();
// I created Application and Worksheet objects before try/catch,
// so that i can close them in finnaly block.
// It's IMPORTANT to release these COM objects!!
try
{
// ------------------------------------------------
// Creation of header cells
// ------------------------------------------------
workSheet.Cells[1, "A"]="origen";
workSheet.Cells[1, "B"]="column1";
workSheet.Cells[1, "C"]="referencia1";
workSheet.Cells[1, "D"]="referencia2";
workSheet.Cells[1, "E"]="referencia3";
workSheet.Cells[1, "F"]="presupuesto";
workSheet.Cells[1, "G"]="codcapi";
workSheet.Cells[1, "H"]="capitulo";
workSheet.Cells[1, "I"]="codunit";
workSheet.Cells[1, "J"]="unitario";
workSheet.Cells[1, "K"]="undunita";
workSheet.Cells[1, "L"]="cantxppto";
workSheet.Cells[1, "M"]="codinsu";
workSheet.Cells[1, "N"]="insutipo";
workSheet.Cells[1, "O"]="insumo";
workSheet.Cells[1, "P"]="unidinsu";
workSheet.Cells[1, "Q"]="ctrlinven";
workSheet.Cells[1, "R"]="validación";
workSheet.Cells[1, "S"]="precioppto";
workSheet.Cells[1, "T"]="consumounitario";
workSheet.Cells[1, "U"]="consumototal";
workSheet.Cells[1, "V"]="adic";
workSheet.Cells[1, "W"]="precioaut";
workSheet.Cells[1, "X"]="PrecioCompra";
workSheet.Cells[1, "Y"]="PrecioEntrado";
workSheet.Cells[1, "Z"]="Ped";
workSheet.Cells[1, "AA"]="aprob";
workSheet.Cells[1, "AB"]="comp";
workSheet.Cells[1, "AC"]="ent";
workSheet.Cells[1, "AD"]="sali";
workSheet.Cells[1, "AE"]="traslado";
workSheet.Cells[1, "AF"]="xcomp";
workSheet.Cells[1, "AG"]="xent";
workSheet.Cells[1, "AH"]="saldoxunit";
workSheet.Cells[1, "AI"]="saldoxppto";
workSheet.Cells[1, "AJ"]="vlrEnt";
workSheet.Cells[1, "AK"]="vlrEnttraslado";
workSheet.Cells[1, "AL"]="VlrCompradoxent";
workSheet.Cells[1, "AM"]="vlrxcomp";
workSheet.Cells[1, "AN"]="VlrTraslado";
workSheet.Cells[1, "AO"]="vlrproy";
workSheet.Cells[1, "AP"]="Vlrini";
workSheet.Cells[1, "AQ"]="vlrejec";
workSheet2.Cells[1, "A"] = "referencia1";
// ------------------------------------------------
// Populate sheet with some real data from "cars" list
// ------------------------------------------------
int row = 2; // start row (in row 1 are header cells)
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
workSheet.Cells[row, "A"] = dt.Rows[i].Field<string>("origen");
workSheet.Cells[row, "B"] = dt.Rows[i].Field<DateTime>("column1");
workSheet.Cells[row, "C"] = dt.Rows[i].Field<string>("referencia1");
workSheet.Cells[row, "D"] = dt.Rows[i].Field<string>("referencia2");
workSheet.Cells[row, "E"] = dt.Rows[i].Field<string>("referencia3");
/* workSheet.Cells[row, "F"] = dt.Rows[i].Field<string>("presupuesto");
workSheet.Cells[row, "G"] = dt.Rows[i].Field<string>("codcapi");
workSheet.Cells[row, "H"] = dt.Rows[i].Field<string>("capitulo");
workSheet.Cells[row, "I"] = dt.Rows[i].Field<string>("codunit");
workSheet.Cells[row, "J"] = dt.Rows[i].Field<string>("unitario");
workSheet.Cells[row, "K"] = dt.Rows[i].Field<string>("undunita");
workSheet.Cells[row, "L"] = dt.Rows[i].Field<decimal>("cantxppto");
workSheet.Cells[row, "M"] = dt.Rows[i].Field<string>("codinsu");
workSheet.Cells[row, "N"] = dt.Rows[i].Field<string>("insutipo");
// workSheet.Cells[row, "O"] = dt.Rows[i].Field<string>("Proyecto");
workSheet.Cells[row, "P"] = dt.Rows[i].Field<string>("insumo");
workSheet.Cells[row, "Q"] = dt.Rows[i].Field<string>("unidinsu");
workSheet.Cells[row, "R"] = dt.Rows[i].Field<string>("ctrlinven");
workSheet.Cells[row, "S"] = dt.Rows[i].Field<string>("validación");
workSheet.Cells[row, "T"] = dt.Rows[i].Field<decimal>("precioppto");
workSheet.Cells[row, "U"] = dt.Rows[i].Field<decimal>("consumounitario");
workSheet.Cells[row, "V"] = dt.Rows[i].Field<decimal>("consumototal");
workSheet.Cells[row, "W"] = dt.Rows[i].Field<decimal>("precioaut");
workSheet.Cells[row, "X"] = dt.Rows[i].Field<decimal>("PrecioCompra");
workSheet.Cells[row, "Y"] = dt.Rows[i].Field<decimal>("PrecioEntrado");
workSheet.Cells[row, "Z"] = dt.Rows[i].Field<decimal>("Ped");
workSheet.Cells[row, "AA"] = dt.Rows[i].Field<decimal>("aprob");
workSheet.Cells[row, "AB"] = dt.Rows[i].Field<decimal>("comp");
workSheet.Cells[row, "AC"] = dt.Rows[i].Field<decimal>("traslado");
workSheet.Cells[row, "AD"] = dt.Rows[i].Field<decimal>("sali");
workSheet.Cells[row, "AE"] = dt.Rows[i].Field<decimal>("xcomp");
workSheet.Cells[row, "AF"] = dt.Rows[i].Field<decimal>("xent");
workSheet.Cells[row, "AG"] = dt.Rows[i].Field<decimal>("saldoxunit");
workSheet.Cells[row, "AH"] = dt.Rows[i].Field<decimal>("saldoxppto");
workSheet.Cells[row, "AI"] = dt.Rows[i].Field<decimal>("vlrEnt");
workSheet.Cells[row, "AJ"] = dt.Rows[i].Field<decimal>("vlrEnttraslado");
workSheet.Cells[row, "AK"] = dt.Rows[i].Field<decimal>("VlrCompradoxent");
workSheet.Cells[row, "AL"] = dt.Rows[i].Field<decimal>("vlrxcomp");
workSheet.Cells[row, "AM"] = dt.Rows[i].Field<decimal>("VlrTraslado");
workSheet.Cells[row, "AN"] = dt.Rows[i].Field<decimal>("vlrproy");
workSheet.Cells[row, "AO"] = dt.Rows[i].Field<decimal>("Vlrini");
//workSheet.Cells[row, "AP"] = dt.Rows[i].Field<decimal>("Fecha");
workSheet.Cells[row, "AQ"] = Convert.ToString(dt.Rows[i].Field<decimal>("vlrejec"));
workSheet.Cells[row].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);*/
//57
//124
row++;
}
int row2=2;
for (int i = 0; i <= dt2.Rows.Count - 1; i++)
{
workSheet2.Cells[row2, "A"] = dt2.Rows[i].Field<string>("referencia1");
workSheet2.Cells[row2, "D"] = dt2.Rows[i].Field<string>("referencia2");
workSheet2.Cells[row2, "E"] = dt2.Rows[i].Field<string>("referencia3");
workSheet2.Cells[row2, "F"] = dt2.Rows[i].Field<string>("presupuesto");
workSheet2.Cells[row2, "G"] = dt2.Rows[i].Field<string>("codcapi");
/* workSheet2.Cells[row2, "H"] = dt2.Rows[i].Field<string>("capitulo");
workSheet2.Cells[row2, "I"] = dt2.Rows[i].Field<string>("codunit");
workSheet2.Cells[row2, "K"] = dt2.Rows[i].Field<string>("undunita");
workSheet2.Cells[row2, "L"] = dt2.Rows[i].Field<decimal>("cantxppto");
workSheet2.Cells[row2, "M"] = dt2.Rows[i].Field<string>("codinsu");
workSheet2.Cells[row2, "N"] = dt2.Rows[i].Field<string>("insutipo");
// workSheet.Cells[row, "O"] = dt.Rows[i].Field<string>("Proyecto");
workSheet2.Cells[row2, "P"] = dt2.Rows[i].Field<string>("insumo");
workSheet2.Cells[row2, "Q"] = dt2.Rows[i].Field<string>("unidinsu");
workSheet2.Cells[row2, "R"] = dt2.Rows[i].Field<string>("ctrlinven");
workSheet2.Cells[row2, "S"] = dt2.Rows[i].Field<string>("validación");
workSheet2.Cells[row2, "T"] = dt2.Rows[i].Field<decimal>("precioppto");
workSheet2.Cells[row2, "U"] = dt2.Rows[i].Field<decimal>("consumounitario");
workSheet2.Cells[row2, "V"] = dt2.Rows[i].Field<decimal>("consumototal");
workSheet2.Cells[row2, "W"] = dt2.Rows[i].Field<decimal>("precioaut");
workSheet2.Cells[row2, "X"] = dt2.Rows[i].Field<decimal>("PrecioCompra");
workSheet2.Cells[row2, "Y"] = dt2.Rows[i].Field<decimal>("PrecioEntrado");
workSheet2.Cells[row2, "Z"] = dt2.Rows[i].Field<decimal>("Ped");
workSheet2.Cells[row2, "AA"] = dt2.Rows[i].Field<decimal>("aprob");
workSheet2.Cells[row2, "AB"] = dt2.Rows[i].Field<decimal>("comp");
workSheet2.Cells[row2, "AC"] = dt2.Rows[i].Field<decimal>("traslado");
workSheet2.Cells[row2, "AD"] = dt2.Rows[i].Field<decimal>("sali");
workSheet2.Cells[row2, "AE"] = dt2.Rows[i].Field<decimal>("xcomp");
workSheet2.Cells[row2, "AF"] = dt2.Rows[i].Field<decimal>("xent");
workSheet2.Cells[row2, "AG"] = dt2.Rows[i].Field<decimal>("saldoxunit");
workSheet2.Cells[row2, "AH"] = dt2.Rows[i].Field<decimal>("saldoxppto");
workSheet2.Cells[row2, "AI"] = dt2.Rows[i].Field<decimal>("vlrEnt");
workSheet2.Cells[row2, "AJ"] = dt2.Rows[i].Field<decimal>("vlrEnttraslado");
workSheet2.Cells[row2, "AK"] = dt2.Rows[i].Field<decimal>("VlrCompradoxent");
workSheet2.Cells[row2, "AL"] = dt2.Rows[i].Field<decimal>("vlrxcomp");
workSheet2.Cells[row2, "AM"] = dt2.Rows[i].Field<decimal>("VlrTraslado");
workSheet2.Cells[row2, "AN"] = dt2.Rows[i].Field<decimal>("vlrproy");
workSheet2.Cells[row2, "AO"] = dt2.Rows[i].Field<decimal>("Vlrini");
workSheet2.Cells[row2, "AP"] = dt2.Rows[i].Field<int>("IdFecha");
workSheet2.Cells[row2, "AQ"] = Convert.ToString(dt2.Rows[i].Field<decimal>("vlrejec"));*/
//57
//124
row2++;
}
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
workSheet2.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
// Define filename
string fileName = string.Format(@"{0}\ExcelData.xls", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Save this data as a file
workSheet.SaveAs(fileName);
workSheet2.SaveAs(fileName);
// Display SUCCESS message
}
catch (Exception ex)
{
throw ex;
}
finally
{
// Quit Excel application
excel.Quit();
// Release COM objects (very important!)
if (excel != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
if (workSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
// Empty variables
excel = null;
workSheet = null;
// Force garbage collector cleaning
GC.Collect();
}
}
public static void Saveas(DataGridView dgv, string path)
{
Microsoft.Office.Interop.Excel.Application APP = new Microsoft.Office.Interop.Excel.Application();
APP.Application.Workbooks.Add(Type.Missing);
APP.Columns.ColumnWidth = 20;
for (int i = 1; i < dgv.Columns.Count + 1; i++)
{
APP.Cells[1, i] = dgv.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
APP.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value;
}
}
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel File (*.xlsx)|*.xlsx";
saveFileDialog1.InitialDirectory = path;
saveFileDialog1.FilterIndex = 1;
saveFileDialog1.OverwritePrompt = false;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
FileInfo file = new FileInfo(saveFileDialog1.FileName);
if (file.Exists)
{
file.Delete();
}
APP.ActiveWorkbook.SaveAs(saveFileDialog1.FileName.ToString());
APP.ActiveWorkbook.Saved = true;
APP.Quit();
OpenV = true;
}
}
public void WriteConsolidateReportToResponse(DataSet consolidatedReportDS)
{
if (null != consolidatedReportDS)
{
//Response.Write("Beginning report generation ..");
if (consolidatedReportDS.Tables.Count > 0)
{
string fileName = String.Format("{0}.xls", DateTime.Now.ToString("yyyyMMddhhmmss"));
string reportFileName = Server.MapPath(String.Format(@"/{0}", fileName));
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
Excel.Workbook xlWB = xlApp.Workbooks.Add(Type.Missing);
foreach (DataTable dt in consolidatedReportDS.Tables)
{
WriteDataTableToWorkbook(xlWB, dt);
}
xlWB.SaveAs(reportFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
xlWB.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
// Clear the content of the response
Response.ClearContent();
// Add the file name and attachment, which will force the open/cancel/save dialog box to show, to the header
Response.AddHeader("Content-Disposition", "inline; filename=" + reportFileName);
// Add the file size into the response header
Response.AddHeader("Content-Length", new System.IO.FileInfo(reportFileName).Length.ToString());
// Set the ContentType
Response.ContentType = "application/x-excel";
//// Write the file into the response (TransmitFile is for ASP.NET 2.0. In ASP.NET 1.1 you have to use WriteFile instead)
Response.TransmitFile(reportFileName);
// End the response
Response.Flush();
System.IO.File.Delete(reportFileName);
}
}
}
public static void Save(DataGridView dgv, string Savepath)
{
Microsoft.Office.Interop.Excel.Application APP = new Microsoft.Office.Interop.Excel.Application();
APP.Application.Workbooks.Add(Type.Missing);
APP.Columns.ColumnWidth = 20;
for (int i = 1; i < dgv.Columns.Count + 1; i++)
{
APP.Cells[1, i] = dgv.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
APP.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value;
}
}
FileInfo file = new FileInfo(Savepath);
if (file.Exists)
{
file.Delete();
}
OpenV = true;
APP.ActiveWorkbook.SaveAs(Savepath.ToString());
APP.ActiveWorkbook.Saved = true;
APP.Quit();
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
bool detailed = radioButtonDetailledMode.Checked;
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.get_Range("a1", "f1").Merge(false);
Excel.Range chartRange = xlWorkSheet.get_Range("a1", "f1");
chartRange.FormulaR1C1 = string.Format("{0} assets information, media account '{1}'", radioButtonAllAssets.Checked ? "All" : "Selected", _context.Credentials.ClientId);
chartRange.VerticalAlignment = 3;
chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
chartRange.Font.Size = 20;
xlWorkSheet.get_Range("a2", "f2").Merge(false);
Excel.Range chartRange2 = xlWorkSheet.get_Range("a2", "f2");
chartRange2.FormulaR1C1 = string.Format("Exported with Azure Media Services Explorer v{0} on {1}. Dates are {2}.",
Assembly.GetExecutingAssembly().GetName().Version.ToString(),
checkBoxLocalTime.Checked ? DateTime.Now.ToString() : DateTime.UtcNow.ToString(),
checkBoxLocalTime.Checked ? "local" : "UTC based"
);
chartRange2.VerticalAlignment = 3;
chartRange2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
chartRange2.Font.Size = 12;
int row = 4;
int index = 1;
xlWorkSheet.Cells[row, index++] = "Asset Name";
xlWorkSheet.Cells[row, index++] = "Id";
xlWorkSheet.Cells[row, index++] = "Last Modified";
xlWorkSheet.Cells[row, index++] = "Type";
xlWorkSheet.Cells[row, index++] = "Size";
int backindex = index;
_context.StreamingEndpoints.ToList().ForEach(se =>
xlWorkSheet.Cells[row, index++] = "Streaming URL"
);
index = backindex + _context.StreamingEndpoints.Count();
xlWorkSheet.Cells[row, index++] = "Expiration time";
if (detailed)
{
xlWorkSheet.Cells[row, index++] = "Alternate Id";
xlWorkSheet.Cells[row, index++] = "Storage Account";
xlWorkSheet.Cells[row, index++] = "Streaming Locators Count";
xlWorkSheet.Cells[row, index++] = "Streaming Min Expiration time";
xlWorkSheet.Cells[row, index++] = "Streaming Max Expiration time";
xlWorkSheet.Cells[row, index++] = "SAS Locators Count";
xlWorkSheet.Cells[row, index++] = "SAS Min Expiration time";
xlWorkSheet.Cells[row, index++] = "SAS Max Expiration time";
xlWorkSheet.Cells[row, index++] = "Dynamic encryption";
xlWorkSheet.Cells[row, index++] = "Asset filters count";
}
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a4");
formatRange.EntireRow.Font.Bold = true;
formatRange.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
if (radioButtonAllAssets.Checked)
{
int skipSize = 0;
int batchSize = 1000;
int currentBatch = 0;
int total = _context.Assets.Count();
int index2 = 1;
while (true)
{
IQueryable _assetsCollectionQuery = _context.Assets.Skip(skipSize).Take(batchSize);
foreach (IAsset asset in _assetsCollectionQuery)
{
row++;
currentBatch++;
ExportAssetExcel(asset, xlWorkSheet, row, detailed, checkBoxLocalTime.Checked);
backgroundWorker1.ReportProgress(100 * index2 / total, DateTime.Now); //notify progress to main thread. We also pass time information in UserState to cover this property in the example.
//if cancellation is pending, cancel work.
if (backgroundWorker1.CancellationPending)
{
xlApp.DisplayAlerts = false;
xlWorkBook.Close();
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
e.Cancel = true;
return;
}
index2++;
}
if (currentBatch == batchSize)
{
skipSize += batchSize;
currentBatch = 0;
}
else
{
break;
}
}
}
else // Selected or visible asets
{
IEnumerable<IAsset> myassets;
if (radioButtonSelectedAssets.Checked)
{
myassets = _selassets;
}
else
{
myassets = _visibleassets;
}
int total = myassets.Count();
int index3 = 1;
foreach (IAsset asset in myassets)
{
row++;
ExportAssetExcel(asset, xlWorkSheet, row, detailed, checkBoxLocalTime.Checked);
backgroundWorker1.ReportProgress(100 * index3 / total, DateTime.Now); //notify progress to main thread. We also pass time information in UserState to cover this property in the example.
//if cancellation is pending, cancel work.
if (backgroundWorker1.CancellationPending)
{
xlApp.DisplayAlerts = false;
xlWorkBook.Close();
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
e.Cancel = true;
return;
}
index3++;
}
}
// Set the range to fill.
var aRange = xlWorkSheet.get_Range("A4", "Z100");
aRange.EntireColumn.AutoFit();
try
{
xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch
{
MessageBox.Show("Error when saving the Excel file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (checkBoxOpenFileAfterExport.Checked) System.Diagnostics.Process.Start(filename);
}
private void btnToExcel_Click(object sender, EventArgs e)
{
Excel.Application excel = null;
try
{
//DataGridView沒有資料就不執行
if (dgvDataShow.Rows.Count <= 1)
{
MessageBox.Show("沒有可滙出的資料！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//設定滙出後的存檔路徑(儲存在桌面)
string SaveFilePath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) +
@"\即將到期用戶名單" + ".xls";
//new 出一個Excel
excel = new Microsoft.Office.Interop.Excel.Application();
//看的到Excel在工作
excel.Visible = false;
//新增加一工作簿
excel.Application.Workbooks.Add(true);
PGB pgb = new PGB();
pgb.progressBar1.Value = 0;
pgb.progressBar1.Minimum = 0;
pgb.progressBar1.Maximum = dgvDataShow.Rows.Count - 1;
pgb.progressBar1.Step = 1;
pgb.Show();
//寫入欄位名稱
for (int i = 0; i < dgvDataShow.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dgvDataShow.Columns[i].HeaderText;
}
//把DataGridView資料寫到Excel
for (int i = 0; i < dgvDataShow.Rows.Count - 1; i++)
{
pgb.progressBar1.Value++;
Application.DoEvents();
for (int j = 0; j < dgvDataShow.Columns.Count; j++)
{
if (dgvDataShow[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgvDataShow[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgvDataShow[j, i].Value.ToString();
}
}
}
//設定滙出後，欄位寛度自動配合資料調整
excel.Cells.EntireRow.AutoFit();
//自動調整列高
excel.Cells.EntireColumn.AutoFit();
//設置禁止彈出覆蓋或儲存的彈跳視窗
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
//將檔案儲存到SaveFile指定的位置
excel.ActiveWorkbook.SaveCopyAs(SaveFilePath);
if (excel.Application.Version == "11.0")//Office 2007 up
{
excel.ActiveWorkbook.SaveAs(SaveFilePath);
}
else
{
//Office 2003 Up，FileFormat: Excel.XlFileFormat.xlExcel8=>指定Excel 2003 xls格式
excel.ActiveWorkbook.SaveAs(SaveFilePath, FileFormat: Excel.XlFileFormat.xlExcel8);
}
pgb.Dispose();
MessageBox.Show("已成功滙出Excel檔！" + Environment.NewLine + "檔案儲存在您電腦的桌面，檔名：即將到期用戶名單" +
".xls", "完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//關閉工作簿和結束Excel程式
excel.Workbooks.Close();
excel.Quit();
//釋放資源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
}
private void button1_Click(object sender, EventArgs e)
{
string strPath = System.Windows.Forms.Application.StartupPath + "\\Exported File\\List of ID.xlsx";
Excel._Application xl = new Microsoft.Office.Interop.Excel.Application();
Excel._Workbook xb = xl.Workbooks.Open(strPath);
Excel._Worksheet xs;
xs = xb.Worksheets["Sheet1"];
int irows = 3;
for (int r = 0; r <= (gvReport.Rows.Count - 1); r++)
{
System.Windows.Forms.Application.DoEvents();
if (irows > 3)
{
//xlApp.Rows.get_Range("2:2").Select();
xl.Rows["3:3"].Select();
xl.Application.CutCopyMode = Excel.XlCutCopyMode.xlCopy;
xl.Selection.Copy();
xl.Rows[irows + ":" + irows].Select();
xl.Selection.Insert(Shift: -4121);
}
xl.Cells[irows, 1] = gvReport.Rows[r].Cells["Nickname"].Value;
xl.Cells[irows, 2] = gvReport.Rows[r].Cells["LastName"].Value;
xl.Cells[irows, 3] = gvReport.Rows[r].Cells["FirstName"].Value;
xl.Cells[irows, 4] = gvReport.Rows[r].Cells["StudentID"].Value;
xl.Cells[irows, 5] = gvReport.Rows[r].Cells["Birthday"].Value;
xl.Cells[irows, 6] = gvReport.Rows[r].Cells["Age"].Value;
xl.Cells[irows, 7] = gvReport.Rows[r].Cells["Group"].Value;
irows += 1;
//while (pbExport.Value != pbExport.Maximum)
//{
// pbExport.Value += 1;
//}
}
//xl.Visible = true;
//xl.ActiveWindow.SelectedSheets.PrintPreview();
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
string filename = path + "\\" + "ID - " + DateTime.Now.ToString("(MM.dd.yyyy.hh_mm_ss)") + ".xlsx";
xl.ActiveWorkbook.SaveAs(filename);
xl.DisplayAlerts = false;
xb.Close();
xl.Quit();
foreach (Process proc in Process.GetProcessesByName("EXCEL"))
{
proc.Kill();
}
MessageBox.Show("The File Has Been Saved : \n" + filename, "File Export", MessageBoxButtons.OK, MessageBoxIcon.Information);
//pbExport.Value = 0;
//gbExport.Visible = false;
}
private void PerformBuildRequirements()
{
_excel = new Excel.Application();
_word = new Word.Application();
_word.Visible = true;
_excel.Visible = true;
this.BeginInvoke(
new Action<Form1>(s =>
{
btnBuild.Enabled = false;
btnBuild.Text = "Working";
}), new object[] { this });
var spreadsheetPath = tbSpreadsheetPath.Text;
if (File.Exists(spreadsheetPath))
{
var workbook = _excel.Workbooks.Open(spreadsheetPath);
var requirements = new List<Requirement>();
Word.Document doc = BuildRequirementsDoc(workbook, out requirements);
if (cbBuildTrace.Enabled)
{
BuildTraceMatrix(workbook, requirements);
try
{
workbook.Save();
}
catch (Exception) { }
}
try
{
// This call throws when the user elects to 'cancel' the save operation.
doc.Save();
}
catch (Exception) { }
}
else
{
MessageBox.Show("No spreadsheet selected.");
}
foreach (Excel.Workbook wb in _excel.Workbooks)
{
wb.Close();
}
_excel.Quit();
_word.Quit(ref Missing, ref Missing, ref Missing);
this.BeginInvoke(
new Action<Form1>(s =>
{
btnBuild.Enabled = true;
btnBuild.Text = "Build";
}), new object[] { this });
}
/// <summary>
/// 64位系统,把Excel里的数据转换为DataTable,应用引用的com组件：Microsoft.Office.Interop.Excel.dll 读取EXCEL文件
/// </summary>
/// <param name="filenameurl">文件路径</param>
/// <param name="sheetIndex">sheet名称的索引</param>
/// <param name="splitstr"></param>
/// <returns></returns>
public static DataTable ExecleToDataSet(string filenameurl, string sheetName, string splitstr)
{
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
DataTable xlsTable = new DataTable();
object missing = System.Reflection.Missing.Value;
//lauch excel application
XLog.Write("lauch excel application");
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
try
{
if (excel != null)
{
excel.Visible = false;
excel.UserControl = true;
// 以只读的形式打开EXCEL文件
wb = excel.Workbooks.Open(filenameurl, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//取得第一个工作薄
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[sheetName];//.get_Item(sheetIndex);
//取得总记录行数(包括标题列)
int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
int columnsint = ws.UsedRange.Cells.Columns.Count;//得到列数
for (int row = 1; row <= rowsint; row++)
{
if (row == 1)
{
//添加列字段
for (int n = 1; n <= columnsint; n++)
{
string column = (((Microsoft.Office.Interop.Excel.Range)ws.Cells[1, n]).Text.ToString());
xlsTable.Columns.Add(column, typeof(string));
}
}
else
{
//添加行数据
DataRow dr = xlsTable.NewRow();
for (int col = 1; col <= columnsint; col++)
{
dr[col - 1] = ((Microsoft.Office.Interop.Excel.Range)ws.Cells[row, col]).Text.ToString();
}
xlsTable.Rows.Add(dr);
}
}
}
return xlsTable;
}
catch (Exception e)
{
XLog.Write(e.Message);
return xlsTable;
}
finally
{
wb.Close(false, missing, missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
excel.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
private void Read(string path, int sno, string engt_id, string ps_wkno)
{
Microsoft.Office.Interop.Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlNewSheet = null;
Excel.Worksheet xlscvsheet = null;
try
{
#region initial
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
return;
xlWorkbook = xlApp.Workbooks.Open(path, 0, false, 5, "", "",
false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
int boindex = 0, sumindex = 0, cvrindex = 0;
for (int l = 1; l <= xlWorkbook.Sheets.Count; l++)
{
if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "boxoffice")
{
boindex = l;
}
if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "coversheet")
{
cvrindex = l;
}
if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "summary")
{
sumindex = l;
}
}
xlSheets = xlWorkbook.Sheets as Excel.Sheets;
xlNewSheet = (Excel.Worksheet)xlSheets[boindex];
xlNewSheet.Unprotect("7135");
Excel.Worksheet excelsheet3 = null;
excelsheet3 = (Excel.Worksheet)xlSheets[sumindex];
DataTable dt = new DataTable("table1");
DataTable dt1 = new DataTable("table2");
DataTable dtcvr = new DataTable("tablecvr");
DataTable dtchr = new DataTable("tablecvrchg");
string filename = xlNewSheet.Name;
#endregion
#region getrecordid
xlscvsheet = (Excel.Worksheet)xlSheets[cvrindex];
string shname = "", city = "", recid = engt_id;
DateTime opdate, endate;
shname = xlscvsheet.Cells[1, 1].Text;
city = xlscvsheet.Cells[3, 2].Text;
opdate = Convert.ToDateTime(xlscvsheet.Cells[8, 1].Text);
endate = Convert.ToDateTime(xlscvsheet.Cells[8, 2].Text);
xlNewSheet.Cells[sno, 14] = shname;
xlNewSheet.Cells[sno, 15] = city;
xlNewSheet.Cells[sno, 16] = opdate;
xlNewSheet.Cells[sno, 17] = endate;
MasterDataLayer.MasterData objmst = new MasterDataLayer.MasterData();
//DataTable dt_id = new DataTable();
//dt_id = objmst.GetMysql_Recordid(shname, city, opdate, endate);
//if (dt_id.Rows.Count > 0)
//{
// recid = dt_id.Rows[0]["RecordID"].ToString();
//}
//else { recid = "7604"; }
#endregion
#region engt dt creation
dt.Columns.Add("Sno");
dt.Columns.Add("Recordid");
dt.Columns.Add("Show Name");
dt.Columns.Add("City Name");
dt.Columns.Add("Engt_Date");
dt.Columns.Add("Deal_Tax_Ptg");
dt.Columns.Add("Deal_Tax2_Ptg");
dt.Columns.Add("deal_sub_sales_comm");
dt.Columns.Add("deal_ph_sales_comm");
dt.Columns.Add("deal_web_sales_comm");
dt.Columns.Add("deal_cc_sales_comm");
dt.Columns.Add("deal_remote_sales_comm");
dt.Columns.Add("deal_single_tix_comm");
dt.Columns.Add("deal_grp_sales_comm1");
dt.Columns.Add("deal_grp_sales_comm2");
dt.Columns.Add("deal_misc_othr_amt_1");
dt.Columns.Add("deal_misc_othr_amt_2");
dt.Columns.Add("deal_royalty_income");
dt.Columns.Add("deal_incm_wthd_tax_act_amt");
dt.Columns.Add("deal_guarantee_income");
dt.Columns.Add("deal_cmpny_mid_monies_ptg");
dt.Columns.Add("deal_producer_share_split_ptg");
dt.Columns.Add("deal_star_royalty_ptg");
dt.Columns.Add("deal_presenter_share_split_Ptg");
dt.Columns.Add("exp_d_ad_gross_bgt");
dt.Columns.Add("exp_d_ad_gross_act");
dt.Columns.Add("exp_d_stghand_loadin_bgt");
dt.Columns.Add("exp_d_stghand_loadin_act");
dt.Columns.Add("exp_d_stghand_loadout_bgt");
dt.Columns.Add("exp_d_stghand_loadout_act");
dt.Columns.Add("exp_d_stghand_running_bgt");
dt.Columns.Add("exp_d_stghand_running_act");
dt.Columns.Add("exp_d_wardrobe_loadin_bgt");
dt.Columns.Add("exp_d_wardrobe_loadin_act");
dt.Columns.Add("exp_d_wardrobe_loadout_bgt");
dt.Columns.Add("exp_d_wardrobe_loadout_act");
dt.Columns.Add("exp_d_wardrobe_running_bgt");
dt.Columns.Add("exp_d_wardrobe_running_act");
dt.Columns.Add("exp_d_labor_catering_bgt");
dt.Columns.Add("exp_d_labor_catering_act");
dt.Columns.Add("exp_d_musician_bgt");
dt.Columns.Add("exp_d_musician_act");
dt.Columns.Add("exp_d_insurance_per_unit");
dt.Columns.Add("exp_d_insurance_bgt");
dt.Columns.Add("exp_d_insurance_act");
dt.Columns.Add("exp_d_ticket_print_per_unit");
dt.Columns.Add("exp_d_ticket_print_bgt");
dt.Columns.Add("exp_d_ticket_print_act");
dt.Columns.Add("exp_d_other_1_desc");
dt.Columns.Add("exp_d_other_1_bgt");
dt.Columns.Add("exp_d_other_1_act");
dt.Columns.Add("exp_l_ada_expense_bgt");
dt.Columns.Add("exp_l_ada_expense_act");
dt.Columns.Add("exp_l_bo_bgt");
dt.Columns.Add("exp_l_bo_act");
dt.Columns.Add("exp_l_catering_bgt");
dt.Columns.Add("exp_l_catering_act");
dt.Columns.Add("exp_l_equip_rental_bgt");
dt.Columns.Add("exp_l_equip_rental_act");
dt.Columns.Add("exp_l_grp_sales_bgt");
dt.Columns.Add("exp_l_grp_sales_act");
dt.Columns.Add("exp_l_house_staff_bgt");
dt.Columns.Add("exp_l_house_staff_act");
dt.Columns.Add("exp_l_league_fee_bgt");
dt.Columns.Add("exp_l_league_fee_act");
dt.Columns.Add("exp_l_license_bgt");
dt.Columns.Add("exp_l_license_act");
dt.Columns.Add("exp_l_limo_bgt");
dt.Columns.Add("exp_l_limo_act");
dt.Columns.Add("exp_l_orchestra_sh_remove_bgt");
dt.Columns.Add("exp_l_orchestra_sh_remove_act");
dt.Columns.Add("exp_l_presenter_profit_bgt");
dt.Columns.Add("exp_l_presenter_profit_act");
dt.Columns.Add("exp_l_police_bgt");
dt.Columns.Add("exp_l_police_act");
dt.Columns.Add("exp_l_program_bgt");
dt.Columns.Add("exp_l_program_act");
dt.Columns.Add("exp_l_rent_btg");
dt.Columns.Add("exp_l_rent_act");
dt.Columns.Add("exp_l_sound_bgt");
dt.Columns.Add("exp_l_sound_act");
dt.Columns.Add("exp_l_ticket_print_bgt");
dt.Columns.Add("exp_l_ticket_print_act");
dt.Columns.Add("exp_l_phone_bgt");
dt.Columns.Add("exp_l_phone_act");
dt.Columns.Add("exp_l_dryice_bgt");
dt.Columns.Add("exp_l_dryice_act");
dt.Columns.Add("MISCELLANEOUS_bgt");
dt.Columns.Add("MISCELLANEOUS_act");
dt.Columns.Add("exp_l_other1_desc");
dt.Columns.Add("exp_l_other1_bgt");
dt.Columns.Add("exp_l_other1_act");
dt.Columns.Add("exp_l_local_fixed_bgt");
dt.Columns.Add("exp_l_local_fixed_act");
dt.Columns.Add("deal_facility_fee_amt");
dt.Columns.Add("engt_exchange_rate");
dt.Columns.Add("engt_subscription_amt");
dt.Columns.Add("deal_incm_wthd_tax_act_unit");
dt.Columns.Add("ps_schedule_weeks");
dt.Columns.Add("deal_misc_othr_unit_1");
dt.Columns.Add("deal_misc_othr_unit_2");
dt.Columns.Add("deal_presenter_mid_monies_ptg");
dt.Columns.Add("mny_remaining_mid_mny");
#endregion
#region engt val assign
string Show_Name = xlNewSheet.Cells[1, 1].text;
string City_Name = xlNewSheet.Cells[2, 1].text;
string Engt_Date = xlNewSheet.Cells[4, 1].text;
string deal_tax_ptg = xlNewSheet.Cells[14, 2].text;
string deal_tax2_ptg = xlNewSheet.Cells[15, 2].text;
string deal_sub_sales_comm = xlNewSheet.Cells[16, 2].text;
string deal_ph_sales_comm = xlNewSheet.Cells[17, 2].text;
string deal_web_sales_comm = xlNewSheet.Cells[18, 2].text;
string deal_cc_sales_comm = xlNewSheet.Cells[19, 2].text;
string deal_remote_sales_comm = xlNewSheet.Cells[20, 2].text;
string deal_single_tix_comm = xlNewSheet.Cells[21, 2].text;
string deal_grp_sales_comm1 = xlNewSheet.Cells[22, 2].text;
string deal_grp_sales_comm2 = xlNewSheet.Cells[23, 2].text;
string deal_misc_othr_amt_1 = xlNewSheet.Cells[24, 2].text;
string deal_misc_othr_amt_2 = "", deal_facility_fee_amt = "";
string fforother = xlNewSheet.Cells[25, 2].text;
//if (fforother.ToLower() == "facility" == true)
deal_misc_othr_amt_2 = xlNewSheet.Cells[25, 2].text;
//else
deal_facility_fee_amt = "0";// xlNewSheet.Cells[25, 2].text;
string deal_misc_othr_unit_1 = (deal_misc_othr_amt_1.Contains("%") == true) ? "%" : "$";
string deal_misc_othr_unit_2 = (fforother.Contains("%") == true) ? "%" : "$";
string deal_royalty_income = excelsheet3.Cells[31, 2].text;
string deal_incm_wthd_tax_act_amt = excelsheet3.Cells[32, 2].text;
string deal_incm_wthd_tax_act_unit = (deal_incm_wthd_tax_act_amt.Contains("%") == true) ? "%" : "$";
string deal_guarantee_income = excelsheet3.Cells[34, 2].text;
string deal_cmpny_mid_monies_ptg = excelsheet3.Cells[83, 2].text;
string deal_producer_share_split_ptg = excelsheet3.Cells[90, 2].text;
string deal_star_royalty_ptg = excelsheet3.Cells[93, 2].text;
string deal_presenter_share_split_Ptg = excelsheet3.Cells[96, 2].text;
string exp_d_ad_gross_bgt = excelsheet3.Cells[42, 3].text;
string exp_d_ad_gross_act = excelsheet3.Cells[42, 4].text;
string exp_d_stghand_loadin_bgt = excelsheet3.Cells[43, 3].text;
string exp_d_stghand_loadin_act = excelsheet3.Cells[43, 4].text;
string exp_d_stghand_loadout_bgt = excelsheet3.Cells[44, 3].text;
string exp_d_stghand_loadout_act = excelsheet3.Cells[44, 4].text;
string exp_d_stghand_running_bgt = excelsheet3.Cells[45, 3].text;
string exp_d_stghand_running_act = excelsheet3.Cells[45, 4].text;
string exp_d_wardrobe_loadin_bgt = excelsheet3.Cells[46, 3].text;
string exp_d_wardrobe_loadin_act = excelsheet3.Cells[46, 4].text;
string exp_d_wardrobe_loadout_bgt = excelsheet3.Cells[47, 3].text;
string exp_d_wardrobe_loadout_act = excelsheet3.Cells[47, 4].text;
string exp_d_wardrobe_running_bgt = excelsheet3.Cells[48, 3].text;
string exp_d_wardrobe_running_act = excelsheet3.Cells[48, 4].text;
string exp_d_labor_catering_bgt = excelsheet3.Cells[49, 3].text;
string exp_d_labor_catering_act = excelsheet3.Cells[49, 4].text;
string exp_d_musician_bgt = excelsheet3.Cells[50, 3].text;
string exp_d_musician_act = excelsheet3.Cells[50, 4].text;
string exp_d_insurance_per_unit = excelsheet3.Cells[51, 2].text;
string exp_d_insurance_bgt = excelsheet3.Cells[51, 3].text;
string exp_d_insurance_act = excelsheet3.Cells[51, 4].text;
string exp_d_ticket_print_per_unit = excelsheet3.Cells[52, 2].text;
string exp_d_ticket_print_bgt = excelsheet3.Cells[52, 3].text;
string exp_d_ticket_print_act = excelsheet3.Cells[52, 4].text;
string exp_d_other_1_desc = excelsheet3.Cells[53, 2].text;
string exp_d_other_1_bgt = excelsheet3.Cells[53, 3].text;
string exp_d_other_1_act = excelsheet3.Cells[53, 4].text;
string exp_l_ada_expense_bgt = excelsheet3.Cells[56, 3].text;
string exp_l_ada_expense_act = excelsheet3.Cells[56, 4].text;
string exp_l_bo_bgt = excelsheet3.Cells[57, 3].text;
string exp_l_bo_act = excelsheet3.Cells[57, 4].text;
string exp_l_catering_bgt = excelsheet3.Cells[58, 3].text;
string exp_l_catering_act = excelsheet3.Cells[58, 4].text;
string exp_l_equip_rental_bgt = excelsheet3.Cells[59, 3].text;
string exp_l_equip_rental_act = excelsheet3.Cells[59, 4].text;
string exp_l_grp_sales_bgt = excelsheet3.Cells[60, 3].text;
string exp_l_grp_sales_act = excelsheet3.Cells[60, 4].text;
string exp_l_house_staff_bgt = excelsheet3.Cells[61, 3].text;
string exp_l_house_staff_act = excelsheet3.Cells[61, 4].text;
string exp_l_league_fee_bgt = excelsheet3.Cells[62, 3].text;
string exp_l_league_fee_act = excelsheet3.Cells[62, 4].text;
string exp_l_license_bgt = excelsheet3.Cells[63, 3].text;
string exp_l_license_act = excelsheet3.Cells[63, 4].text;
string exp_l_limo_bgt = excelsheet3.Cells[64, 3].text;
string exp_l_limo_act = excelsheet3.Cells[64, 4].text;
string exp_l_orchestra_sh_remove_bgt = excelsheet3.Cells[65, 3].text;
string exp_l_orchestra_sh_remove_act = excelsheet3.Cells[65, 4].text;
string exp_l_presenter_profit_bgt = excelsheet3.Cells[66, 3].text;
string exp_l_presenter_profit_act = excelsheet3.Cells[66, 4].text;
string exp_l_police_bgt = excelsheet3.Cells[67, 3].text;
string exp_l_police_act = excelsheet3.Cells[67, 4].text;
string exp_l_program_bgt = excelsheet3.Cells[68, 3].text;
string exp_l_program_act = excelsheet3.Cells[68, 4].text;
string exp_l_rent_btg = excelsheet3.Cells[69, 3].text;
string exp_l_rent_act = excelsheet3.Cells[69, 4].text;
string exp_l_sound_bgt = excelsheet3.Cells[70, 3].text;
string exp_l_sound_act = excelsheet3.Cells[70, 4].text;
string exp_l_ticket_print_bgt = excelsheet3.Cells[71, 3].text;
string exp_l_ticket_print_act = excelsheet3.Cells[71, 4].text;
string exp_l_phone_bgt = excelsheet3.Cells[72, 3].text;
string exp_l_phone_act = excelsheet3.Cells[72, 4].text;
string exp_l_dryice_bgt = excelsheet3.Cells[73, 3].text;
string exp_l_dryice_act = excelsheet3.Cells[73, 4].text;
string misc1 = excelsheet3.Cells[74, 3].text;
string misc2 = excelsheet3.Cells[74, 4].text;
string exp_l_other1_desc = excelsheet3.Cells[75, 2].text;
string exp_l_other1_bgt = excelsheet3.Cells[75, 3].text;
string exp_l_other1_act = excelsheet3.Cells[75, 4].text;
string exp_l_local_fixed_bgt = excelsheet3.Cells[76, 3].text;
string exp_l_local_fixed_act = excelsheet3.Cells[76, 4].text;
// string deal_facility_fee_amt = xlNewSheet.Cells[25, 2].text;
string engt_exchange_rate = excelsheet3.Cells[3, 7].text;
string engt_subscription_amt = (string.IsNullOrEmpty(xlNewSheet.Cells[32, 15].text) == true) ? xlNewSheet.Cells[32, 14].text : xlNewSheet.Cells[32, 15].text;
string deal_presenter_mid_monies_ptg = excelsheet3.Cells[83, 4].text;
string mny_remaining_mid_mny = excelsheet3.Cells[81, 5].text;
#endregion
#region engt row add
dt.Rows.Add(sno, recid,
Show_Name,
City_Name,
Engt_Date,
deal_tax_ptg.AutoformatDecimal(),
deal_tax2_ptg.AutoformatDecimal(),
deal_sub_sales_comm.AutoformatDecimal(),
deal_ph_sales_comm.AutoformatDecimal(),
deal_web_sales_comm.AutoformatDecimal(),
deal_cc_sales_comm.AutoformatDecimal(),
deal_remote_sales_comm.AutoformatDecimal(),
deal_single_tix_comm.AutoformatDecimal(),
deal_grp_sales_comm1.AutoformatDecimal(),
deal_grp_sales_comm2.AutoformatDecimal(),
deal_misc_othr_amt_1.AutoformatDecimal(),
deal_misc_othr_amt_2.AutoformatDecimal(),
deal_royalty_income.AutoformatDecimal(),
deal_incm_wthd_tax_act_amt.AutoformatDecimal(),
deal_guarantee_income.AutoformatDecimal(),
deal_cmpny_mid_monies_ptg.AutoformatDecimal(),
deal_producer_share_split_ptg.AutoformatDecimal(),
deal_star_royalty_ptg.AutoformatDecimal(),
deal_presenter_share_split_Ptg.AutoformatDecimal(),
exp_d_ad_gross_bgt.AutoformatDecimal(),
exp_d_ad_gross_act.AutoformatDecimal(),
exp_d_stghand_loadin_bgt.AutoformatDecimal(),
exp_d_stghand_loadin_act.AutoformatDecimal(),
exp_d_stghand_loadout_bgt.AutoformatDecimal(),
exp_d_stghand_loadout_act.AutoformatDecimal(),
exp_d_stghand_running_bgt.AutoformatDecimal(),
exp_d_stghand_running_act.AutoformatDecimal(),
exp_d_wardrobe_loadin_bgt.AutoformatDecimal(),
exp_d_wardrobe_loadin_act.AutoformatDecimal(),
exp_d_wardrobe_loadout_bgt.AutoformatDecimal(),
exp_d_wardrobe_loadout_act.AutoformatDecimal(),
exp_d_wardrobe_running_bgt.AutoformatDecimal(),
exp_d_wardrobe_running_act.AutoformatDecimal(),
exp_d_labor_catering_bgt.AutoformatDecimal(),
exp_d_labor_catering_act.AutoformatDecimal(),
exp_d_musician_bgt.AutoformatDecimal(),
exp_d_musician_act.AutoformatDecimal(),
exp_d_insurance_per_unit.AutoformatDecimal(),
exp_d_insurance_bgt.AutoformatDecimal(),
exp_d_insurance_act.AutoformatDecimal(),
exp_d_ticket_print_per_unit.AutoformatDecimal(),
exp_d_ticket_print_bgt.AutoformatDecimal(),
exp_d_ticket_print_act.AutoformatDecimal(),
exp_d_other_1_desc.AutoformatDecimal(),
exp_d_other_1_bgt.AutoformatDecimal(),
exp_d_other_1_act.AutoformatDecimal(),
exp_l_ada_expense_bgt.AutoformatDecimal(),
exp_l_ada_expense_act.AutoformatDecimal(),
exp_l_bo_bgt.AutoformatDecimal(),
exp_l_bo_act.AutoformatDecimal(),
exp_l_catering_bgt.AutoformatDecimal(),
exp_l_catering_act.AutoformatDecimal(),
exp_l_equip_rental_bgt.AutoformatDecimal(),
exp_l_equip_rental_act.AutoformatDecimal(),
exp_l_grp_sales_bgt.AutoformatDecimal(),
exp_l_grp_sales_act.AutoformatDecimal(),
exp_l_house_staff_bgt.AutoformatDecimal(),
exp_l_house_staff_act.AutoformatDecimal(),
exp_l_league_fee_bgt.AutoformatDecimal(),
exp_l_league_fee_act.AutoformatDecimal(),
exp_l_license_bgt.AutoformatDecimal(),
exp_l_license_act.AutoformatDecimal(),
exp_l_limo_bgt.AutoformatDecimal(),
exp_l_limo_act.AutoformatDecimal(),
exp_l_orchestra_sh_remove_bgt.AutoformatDecimal(),
exp_l_orchestra_sh_remove_act.AutoformatDecimal(),
exp_l_presenter_profit_bgt.AutoformatDecimal(),
exp_l_presenter_profit_act.AutoformatDecimal(),
exp_l_police_bgt.AutoformatDecimal(),
exp_l_police_act.AutoformatDecimal(),
exp_l_program_bgt.AutoformatDecimal(),
exp_l_program_act.AutoformatDecimal(),
exp_l_rent_btg.AutoformatDecimal(),
exp_l_rent_act.AutoformatDecimal(),
exp_l_sound_bgt.AutoformatDecimal(),
exp_l_sound_act.AutoformatDecimal(),
exp_l_ticket_print_bgt.AutoformatDecimal(),
exp_l_ticket_print_act.AutoformatDecimal(),
exp_l_phone_bgt.AutoformatDecimal(),
exp_l_phone_act.AutoformatDecimal(),
exp_l_dryice_bgt.AutoformatDecimal(),
exp_l_dryice_act.AutoformatDecimal(),
misc1.AutoformatDecimal(),
misc2.AutoformatDecimal(),
exp_l_other1_desc.AutoformatDecimal(),
exp_l_other1_bgt.AutoformatDecimal(),
exp_l_other1_act.AutoformatDecimal(),
exp_l_local_fixed_bgt.AutoformatDecimal(),
exp_l_local_fixed_act.AutoformatDecimal(),
deal_facility_fee_amt.AutoformatDecimal(),
engt_exchange_rate.AutoformatDecimal(),
engt_subscription_amt.AutoformatDecimal(), deal_incm_wthd_tax_act_unit, ps_wkno, deal_misc_othr_unit_1, deal_misc_othr_unit_2,
deal_presenter_mid_monies_ptg.AutoformatDecimal(),
mny_remaining_mid_mny.AutoformatDecimal()
);
#endregion
#region schedule dt creation
dt1.Columns.Add("Sno");
dt1.Columns.Add("Recordid");
dt1.Columns.Add("Schedule_Type");
dt1.Columns.Add("schedule_date");
dt1.Columns.Add("schedule_st_time");
dt1.Columns.Add("bo_drop_count");
dt1.Columns.Add("bo_paid_attendance");
dt1.Columns.Add("bo_comps");
dt1.Columns.Add("bo_gross_sales");
dt1.Columns.Add("bo_sub_gross_rcpt");
dt1.Columns.Add("bo_ph_gross_rcpt");
dt1.Columns.Add("bo_web_gross_rcpt");
dt1.Columns.Add("bo_cc_gross_rcpt");
dt1.Columns.Add("bo_outlet_gross_rcpt");
dt1.Columns.Add("bo_single_tix_gross_rcpt");
dt1.Columns.Add("bo_small_group_gross_rcpt");
dt1.Columns.Add("bo_large_group_gross_rcpt");
dt1.Columns.Add("bo_other_per_gross_rcpt");
dt1.Columns.Add("bo_other_usd_gross_rcpt");
dt1.Columns.Add("bo_sub_t_sold");
dt1.Columns.Add("bo_ph_t_sold");
dt1.Columns.Add("bo_web_t_sold");
dt1.Columns.Add("bo_cc_t_sold");
dt1.Columns.Add("bo_outlet_t_sold");
dt1.Columns.Add("bo_single_tix_t_sold");
dt1.Columns.Add("bo_small_group_t_sold");
dt1.Columns.Add("dsct_sub1_per");
dt1.Columns.Add("dsct_sub1_tickets");
dt1.Columns.Add("dsct_sub2_per");
dt1.Columns.Add("dsct_sub2_tickets");
dt1.Columns.Add("dsct_sub3_per");
dt1.Columns.Add("dsct_sub3_tickets");
dt1.Columns.Add("dsct_sub4_per");
dt1.Columns.Add("dsct_sub4_tickets");
dt1.Columns.Add("dsct_sub5_per");
dt1.Columns.Add("dsct_sub5_tickets");
dt1.Columns.Add("dsct_sub6_per");
dt1.Columns.Add("dsct_sub6_tickets");
dt1.Columns.Add("dsct_sml_grp_per");
dt1.Columns.Add("dsct_sml_grp_tickets");
dt1.Columns.Add("dsct_lrg_grp_per");
dt1.Columns.Add("dsct_lrg_grp_tickets");
dt1.Columns.Add("dsct_misc1_per");
dt1.Columns.Add("dsct_misc1_tickets");
dt1.Columns.Add("dsct_misc2_per");
dt1.Columns.Add("dsct_misc2_tickets");
dt1.Columns.Add("dsct_misc3_per");
dt1.Columns.Add("dsct_misc3_tickets");
dt1.Columns.Add("dsct_misc4_per");
dt1.Columns.Add("dsct_misc4_tickets");
dt1.Columns.Add("dsct_demand_price");
dt1.Columns.Add("Scale1 ticket");
dt1.Columns.Add("Scale2 ticket");
dt1.Columns.Add("Scale3 ticket");
dt1.Columns.Add("Scale4 ticket");
dt1.Columns.Add("Scale5 ticket");
dt1.Columns.Add("Scale6 ticket");
dt1.Columns.Add("Scale7 ticket");
dt1.Columns.Add("Scale8 ticket");
dt1.Columns.Add("Scale9 ticket");
dt1.Columns.Add("Scale10 ticket");
dt1.Columns.Add("Scale11 ticket");
dt1.Columns.Add("Scale1 price");
dt1.Columns.Add("Scale2 price");
dt1.Columns.Add("Scale3 price");
dt1.Columns.Add("Scale4 price");
dt1.Columns.Add("Scale5 price");
dt1.Columns.Add("Scale6 price");
dt1.Columns.Add("Scale7 price");
dt1.Columns.Add("Scale8 price");
dt1.Columns.Add("Scale9 price");
dt1.Columns.Add("Scale10 price");
dt1.Columns.Add("Scale11 price");
dt1.Columns.Add("dsct_misc5_per");
dt1.Columns.Add("dsct_misc5_tickets");
#endregion
#region insert schedule using for loop
string stype = "", perday = "", perday_p = "";
bool perflag;
DateTime opdateinc = opdate, Tdate = opdate;
for (int i = 3; i <= 12; i++)
{
stype = Convert.ToString(xlNewSheet.Cells[1, i].value);
perflag = stype.ToLower().Contains("perf");
perday = Convert.ToString(xlNewSheet.Cells[2, i].value).ToLower();
perday = (perday.Length > 3) ? perday.Substring(0, 3) : perday;
opdateinc = (perday_p == perday || i == 3) ? Tdate : opdateinc.AddDays(1);
perday_p = perday;
stype = (perday != "day" && perflag == true) ? Convert.ToString(opdateinc) : stype;
perflag = stype.ToLower().Contains("perf");
if (perflag == false)
{
//dt1.Rows.Add("Performance " + (i - 2).ToString(),
// stype,
// xlNewSheet.Cells[3, i].text,
Tdate = Convert.ToDateTime(stype);
string bo_drop_count = xlNewSheet.Cells[6, i].text;
string bo_paid_attendance = xlNewSheet.Cells[7, i].text;
string bo_comps = xlNewSheet.Cells[8, i].text;
string bo_gross_sales = xlNewSheet.Cells[13, i].text;
string bo_sub_gross_rcpt = xlNewSheet.Cells[32, i].text;
string bo_ph_gross_rcpt = xlNewSheet.Cells[33, i].text;
string bo_web_gross_rcpt = xlNewSheet.Cells[34, i].text;
string bo_cc_gross_rcpt = xlNewSheet.Cells[35, i].text;
string bo_outlet_gross_rcpt = xlNewSheet.Cells[36, i].text;
string bo_single_tix_gross_rcpt = xlNewSheet.Cells[37, i].text;
string bo_small_group_gross_rcpt = xlNewSheet.Cells[38, i].text;
string bo_large_group_gross_rcpt = xlNewSheet.Cells[39, i].text;
string bo_other_per_gross_rcpt = xlNewSheet.Cells[40, i].text;
string bo_other_usd_gross_rcpt = xlNewSheet.Cells[41, i].text;
string bo_sub_t_sold = xlNewSheet.Cells[45, i].text;
string bo_ph_t_sold = xlNewSheet.Cells[46, i].text;
string bo_web_t_sold = xlNewSheet.Cells[47, i].text;
string bo_cc_t_sold = xlNewSheet.Cells[48, i].text;
string bo_outlet_t_sold = xlNewSheet.Cells[49, i].text;
string bo_single_tix_t_sold = xlNewSheet.Cells[50, i].text;
string bo_small_group_t_sold = xlNewSheet.Cells[51, i].text;
string dsct_sub1_per = xlNewSheet.Cells[58, i].text;
string dsct_sub1_tickets = xlNewSheet.Cells[59, i].text;
string dsct_sub2_per = xlNewSheet.Cells[60, i].text;
string dsct_sub2_tickets = xlNewSheet.Cells[61, i].text;
string dsct_sub3_per = xlNewSheet.Cells[62, i].text;
string dsct_sub3_tickets = xlNewSheet.Cells[63, i].text;
string dsct_sub4_per = xlNewSheet.Cells[64, i].text;
string dsct_sub4_tickets = xlNewSheet.Cells[65, i].text;
string dsct_sub5_per = xlNewSheet.Cells[66, i].text;
string dsct_sub5_tickets = xlNewSheet.Cells[67, i].text;
string dsct_sub6_per = xlNewSheet.Cells[68, i].text;
string dsct_sub6_tickets = xlNewSheet.Cells[69, i].text;
string dsct_sml_grp_per = xlNewSheet.Cells[72, i].text;
string dsct_sml_grp_tickets = xlNewSheet.Cells[73, i].text;
string dsct_lrg_grp_per = xlNewSheet.Cells[76, i].text;
string dsct_lrg_grp_tickets = xlNewSheet.Cells[77, i].text;
string dsct_misc1_per = xlNewSheet.Cells[80, i].text;
string dsct_misc1_tickets = xlNewSheet.Cells[81, i].text;
string dsct_misc2_per = xlNewSheet.Cells[82, i].text;
string dsct_misc2_tickets = xlNewSheet.Cells[83, i].text;
string dsct_misc3_per = xlNewSheet.Cells[84, i].text;
string dsct_misc3_tickets = xlNewSheet.Cells[85, i].text;
string dsct_misc4_per = xlNewSheet.Cells[86, i].text;
string dsct_misc4_tickets = xlNewSheet.Cells[87, i].text;
string dsct_misc5_per = xlNewSheet.Cells[88, i].text;
string dsct_misc5_tickets = xlNewSheet.Cells[89, i].text;
string dsct_demand_price = xlNewSheet.Cells[97, i].text;
string Scale1ticket = xlNewSheet.Cells[100, 1].text;
string Scale2ticket = xlNewSheet.Cells[101, 1].text;
string Scale3ticket = xlNewSheet.Cells[102, 1].text;
string Scale4ticket = xlNewSheet.Cells[103, 1].text;
string Scale5ticket = xlNewSheet.Cells[104, 1].text;
string Scale6ticket = xlNewSheet.Cells[105, 1].text;
string Scale7ticket = xlNewSheet.Cells[106, 1].text;
string Scale8ticket = xlNewSheet.Cells[107, 1].text;
string Scale9ticket = xlNewSheet.Cells[108, 1].text;
string Scale10ticket = xlNewSheet.Cells[109, 1].text;
string Scale11ticket = xlNewSheet.Cells[110, 1].text;
string Scale1price = xlNewSheet.Cells[100, i].text;
string Scale2price = xlNewSheet.Cells[101, i].text;
string Scale3price = xlNewSheet.Cells[102, i].text;
string Scale4price = xlNewSheet.Cells[103, i].text;
string Scale5price = xlNewSheet.Cells[104, i].text;
string Scale6price = xlNewSheet.Cells[105, i].text;
string Scale7price = xlNewSheet.Cells[106, i].text;
string Scale8price = xlNewSheet.Cells[107, i].text;
string Scale9price = xlNewSheet.Cells[108, i].text;
string Scale10price = xlNewSheet.Cells[109, i].text;
string Scale11price = xlNewSheet.Cells[110, i].text;
#region test
Nullable<decimal> d;
Nullable<Int32> dd;
dd = bo_drop_count.AutoformatInt();
dd = bo_paid_attendance.AutoformatInt();
d = bo_comps.AutoformatInt();
d = bo_gross_sales.AutoformatDecimal();
d = bo_sub_gross_rcpt.AutoformatDecimal();
d = bo_ph_gross_rcpt.AutoformatDecimal();
d = bo_web_gross_rcpt.AutoformatDecimal();
d = bo_cc_gross_rcpt.AutoformatDecimal();
d = bo_outlet_gross_rcpt.AutoformatDecimal();
d = bo_single_tix_gross_rcpt.AutoformatDecimal();
d = bo_small_group_gross_rcpt.AutoformatDecimal();
d = bo_large_group_gross_rcpt.AutoformatDecimal();
d = bo_other_per_gross_rcpt.AutoformatDecimal();
d = bo_other_usd_gross_rcpt.AutoformatDecimal();
d = bo_sub_t_sold.AutoformatInt();
d = bo_ph_t_sold.AutoformatInt();
d = bo_web_t_sold.AutoformatInt();
d = bo_cc_t_sold.AutoformatInt();
d = bo_outlet_t_sold.AutoformatInt();
d = bo_single_tix_t_sold.AutoformatInt();
d = bo_small_group_t_sold.AutoformatInt();
d = dsct_sub1_per.AutoformatDecimal();
d = dsct_sub1_tickets.AutoformatInt();
d = dsct_sub2_per.AutoformatDecimal();
d = dsct_sub2_tickets.AutoformatInt();
d = dsct_sub3_per.AutoformatDecimal();
d = dsct_sub3_tickets.AutoformatInt();
d = dsct_sub4_per.AutoformatDecimal();
d = dsct_sub4_tickets.AutoformatInt();
d = dsct_sub5_per.AutoformatDecimal();
d = dsct_sub5_tickets.AutoformatInt();
d = dsct_sub6_per.AutoformatDecimal();
d = dsct_sub6_tickets.AutoformatInt();
d = dsct_sml_grp_per.AutoformatDecimal();
d = dsct_sml_grp_tickets.AutoformatInt();
d = dsct_lrg_grp_per.AutoformatDecimal();
d = dsct_lrg_grp_tickets.AutoformatInt();
d = dsct_misc1_per.AutoformatDecimal();
d = dsct_misc1_tickets.AutoformatInt();
d = dsct_misc2_per.AutoformatDecimal();
d = dsct_misc2_tickets.AutoformatInt();
d = dsct_misc3_per.AutoformatDecimal();
d = dsct_misc3_tickets.AutoformatInt();
d = dsct_misc4_per.AutoformatDecimal();
d = dsct_misc4_tickets.AutoformatInt();
d = dsct_demand_price.AutoformatDecimal();
d = Scale1price.AutoformatDecimal();
d = Scale2price.AutoformatDecimal();
d = Scale3price.AutoformatDecimal();
d = Scale4price.AutoformatDecimal();
d = Scale5price.AutoformatDecimal();
d = Scale6price.AutoformatDecimal();
d = Scale7price.AutoformatDecimal();
d = Scale8price.AutoformatDecimal();
d = Scale9price.AutoformatDecimal();
d = Scale10price.AutoformatDecimal();
d = Scale11price.AutoformatDecimal();
d = dsct_misc5_per.AutoformatDecimal();
d = dsct_misc5_tickets.AutoformatDecimal();
#endregion
dt1.Rows.Add(sno, recid, "Performance " + (i - 2).ToString(),
stype,
xlNewSheet.Cells[3, i].text,
bo_drop_count.AutoformatInt(),
bo_paid_attendance.AutoformatInt(),
bo_comps.AutoformatInt(),
bo_gross_sales.AutoformatDecimal(),
bo_sub_gross_rcpt.AutoformatDecimal(),
bo_ph_gross_rcpt.AutoformatDecimal(),
bo_web_gross_rcpt.AutoformatDecimal(),
bo_cc_gross_rcpt.AutoformatDecimal(),
bo_outlet_gross_rcpt.AutoformatDecimal(),
bo_single_tix_gross_rcpt.AutoformatDecimal(),
bo_small_group_gross_rcpt.AutoformatDecimal(),
bo_large_group_gross_rcpt.AutoformatDecimal(),
bo_other_per_gross_rcpt.AutoformatDecimal(),
bo_other_usd_gross_rcpt.AutoformatDecimal(),
bo_sub_t_sold.AutoformatInt(),
bo_ph_t_sold.AutoformatInt(),
bo_web_t_sold.AutoformatInt(),
bo_cc_t_sold.AutoformatInt(),
bo_outlet_t_sold.AutoformatInt(),
bo_single_tix_t_sold.AutoformatInt(),
bo_small_group_t_sold.AutoformatInt(),
dsct_sub1_per.AutoformatDecimal(),
dsct_sub1_tickets.AutoformatInt(),
dsct_sub2_per.AutoformatDecimal(),
dsct_sub2_tickets.AutoformatInt(),
dsct_sub3_per.AutoformatDecimal(),
dsct_sub3_tickets.AutoformatInt(),
dsct_sub4_per.AutoformatDecimal(),
dsct_sub4_tickets.AutoformatInt(),
dsct_sub5_per.AutoformatDecimal(),
dsct_sub5_tickets.AutoformatInt(),
dsct_sub6_per.AutoformatDecimal(),
dsct_sub6_tickets.AutoformatInt(),
dsct_sml_grp_per.AutoformatDecimal(),
dsct_sml_grp_tickets.AutoformatInt(),
dsct_lrg_grp_per.AutoformatDecimal(),
dsct_lrg_grp_tickets.AutoformatInt(),
dsct_misc1_per.AutoformatDecimal(),
dsct_misc1_tickets.AutoformatInt(),
dsct_misc2_per.AutoformatDecimal(),
dsct_misc2_tickets.AutoformatInt(),
dsct_misc3_per.AutoformatDecimal(),
dsct_misc3_tickets.AutoformatInt(),
dsct_misc4_per.AutoformatDecimal(),
dsct_misc4_tickets.AutoformatInt(),
dsct_demand_price.AutoformatDecimal(),
ticketseats(Scale1ticket),
ticketseats(Scale2ticket),
ticketseats(Scale3ticket),
ticketseats(Scale4ticket),
ticketseats(Scale5ticket),
ticketseats(Scale6ticket),
ticketseats(Scale7ticket),
ticketseats(Scale8ticket),
ticketseats(Scale9ticket),
ticketseats(Scale10ticket),
ticketseats(Scale11ticket),
Scale1price.AutoformatDecimal(),
Scale2price.AutoformatDecimal(),
Scale3price.AutoformatDecimal(),
Scale4price.AutoformatDecimal(),
Scale5price.AutoformatDecimal(),
Scale6price.AutoformatDecimal(),
Scale7price.AutoformatDecimal(),
Scale8price.AutoformatDecimal(),
Scale9price.AutoformatDecimal(),
Scale10price.AutoformatDecimal(),
Scale11price.AutoformatDecimal(),
dsct_misc5_per.AutoformatDecimal(),
dsct_misc5_tickets.AutoformatDecimal()
);
}
}
#endregion
#region Coversheet dt creation
#region cvrdoc
dtcvr.Columns.Add("cvr_docs_id");
dtcvr.Columns.Add("cvr_engt_id");
dtcvr.Columns.Add("cvr_s_cover_flag");
dtcvr.Columns.Add("cvr_grnty_flag");
dtcvr.Columns.Add("cvr_royalty_flag");
dtcvr.Columns.Add("cvr_ovrg_flag");
dtcvr.Columns.Add("cvr_s_summary_flag");
dtcvr.Columns.Add("cvr_venue_sett_flag");
dtcvr.Columns.Add("cvr_bo_sheet_flag");
dtcvr.Columns.Add("cvr_bo_statements_flag");
dtcvr.Columns.Add("cvr_lbr_bills_flag");
dtcvr.Columns.Add("cvr_musician_bills_flag");
dtcvr.Columns.Add("cvr_local_exp_invoice_flag");
dtcvr.Columns.Add("cvr_ad_flag");
dtcvr.Columns.Add("cvr_contact_flag");
dtcvr.Columns.Add("cvr_s_cover_notes");
dtcvr.Columns.Add("cvr_grnty_notes");
dtcvr.Columns.Add("cvr_royalty_notes");
dtcvr.Columns.Add("cvr_ovrg_notes");
dtcvr.Columns.Add("cvr_s_summary_notes");
dtcvr.Columns.Add("cvr_venue_sett_notes");
dtcvr.Columns.Add("cvr_bo_sheet_notes");
dtcvr.Columns.Add("cvr_bo_statements_notes");
dtcvr.Columns.Add("cvr_lbr_bills_notes");
dtcvr.Columns.Add("cvr_musician_bills_notes");
dtcvr.Columns.Add("cvr_local_exp_invoice_notes");
dtcvr.Columns.Add("cvr_ad_notes");
dtcvr.Columns.Add("cvr_contact_notes");
dtcvr.Columns.Add("email_list");
#endregion
#region cvrchages and receivables
dtchr.Columns.Add("cvr_chgs_id");
dtchr.Columns.Add("cvr_engt_id");
dtchr.Columns.Add("cvr_chgs_desc");
dtchr.Columns.Add("cvr_chgs_amt");
dtchr.Columns.Add("cvr_chgs_check");
dtchr.Columns.Add("cvr_chgs_notes");
dtchr.Columns.Add("cvr_type");
#endregion
#endregion
#region Assign value for coversheet
#region cvrdoc_assign
string cvr_s_cover_flag = "", cvr_grnty_flag = "", cvr_royalty_flag = "", cvr_ovrg_flag = "", cvr_s_summary_flag = "", cvr_venue_sett_flag = "", cvr_bo_sheet_flag = "",
cvr_bo_statements_flag = "", cvr_lbr_bills_flag = "", cvr_musician_bills_flag = "", cvr_local_exp_invoice_flag = "", cvr_ad_flag = "", cvr_contact_flag = "",
cvr_s_cover_notes = "", cvr_grnty_notes = "", cvr_royalty_notes = "", cvr_ovrg_notes = "", cvr_s_summary_notes = "", cvr_venue_sett_notes = "",
cvr_bo_sheet_notes = "", cvr_bo_statements_notes = "", cvr_lbr_bills_notes = "", cvr_musician_bills_notes = "", cvr_local_exp_invoice_notes = "",
cvr_ad_notes = "", cvr_contact_notes = "", email_list = "";
cvr_s_cover_flag = get_cvr_doc_flg(xlscvsheet.Cells[29, 4].text);
cvr_grnty_flag = get_cvr_doc_flg(xlscvsheet.Cells[30, 4].text);
cvr_royalty_flag = get_cvr_doc_flg(xlscvsheet.Cells[31, 4].text);
cvr_ovrg_flag = get_cvr_doc_flg(xlscvsheet.Cells[32, 4].text);
cvr_s_summary_flag = get_cvr_doc_flg(xlscvsheet.Cells[33, 4].text);
cvr_venue_sett_flag = get_cvr_doc_flg(xlscvsheet.Cells[34, 4].text);
cvr_bo_sheet_flag = get_cvr_doc_flg(xlscvsheet.Cells[35, 4].text);
cvr_bo_statements_flag = get_cvr_doc_flg(xlscvsheet.Cells[36, 4].text);
cvr_lbr_bills_flag = get_cvr_doc_flg(xlscvsheet.Cells[37, 4].text);
cvr_musician_bills_flag = get_cvr_doc_flg(xlscvsheet.Cells[38, 4].text);
cvr_local_exp_invoice_flag = get_cvr_doc_flg(xlscvsheet.Cells[39, 4].text);
cvr_ad_flag = get_cvr_doc_flg(xlscvsheet.Cells[40, 4].text);
cvr_contact_flag = get_cvr_doc_flg(xlscvsheet.Cells[41, 4].text);
cvr_s_cover_notes = xlscvsheet.Cells[29, 5].text;
cvr_grnty_notes = xlscvsheet.Cells[30, 5].text;
cvr_royalty_notes = xlscvsheet.Cells[31, 5].text;
cvr_ovrg_notes = xlscvsheet.Cells[32, 5].text;
cvr_s_summary_notes = xlscvsheet.Cells[33, 5].text;
cvr_venue_sett_notes = xlscvsheet.Cells[34, 5].text;
cvr_bo_sheet_notes = xlscvsheet.Cells[35, 5].text;
cvr_bo_statements_notes = xlscvsheet.Cells[36, 5].text;
cvr_lbr_bills_notes = xlscvsheet.Cells[37, 5].text;
cvr_musician_bills_notes = xlscvsheet.Cells[38, 5].text;
cvr_local_exp_invoice_notes = xlscvsheet.Cells[39, 5].text;
cvr_ad_notes = xlscvsheet.Cells[40, 5].text;
cvr_contact_notes = xlscvsheet.Cells[41, 5].text;
email_list = xlscvsheet.Cells[66, 1].text;
dtcvr.Rows.Add(sno, recid, cvr_s_cover_flag, cvr_grnty_flag, cvr_royalty_flag, cvr_ovrg_flag, cvr_s_summary_flag, cvr_venue_sett_flag, cvr_bo_sheet_flag,
cvr_bo_statements_flag, cvr_lbr_bills_flag, cvr_musician_bills_flag, cvr_local_exp_invoice_flag, cvr_ad_flag, cvr_contact_flag, cvr_s_cover_notes,
cvr_grnty_notes, cvr_royalty_notes, cvr_ovrg_notes, cvr_s_summary_notes, cvr_venue_sett_notes, cvr_bo_sheet_notes, cvr_bo_statements_notes, cvr_lbr_bills_notes,
cvr_musician_bills_notes, cvr_local_exp_invoice_notes, cvr_ad_notes, cvr_contact_notes, email_list);
#endregion
#region cvrcharges and receivables assign
string cvr_chgs_desc, cvr_chgs_amt, cvr_chgs_check, cvr_chgs_notes, cvr_type = "c";
for (int c = 44; c <= 52; c++)
{
cvr_chgs_desc = xlscvsheet.Cells[c, 1].text;
cvr_chgs_amt = xlscvsheet.Cells[c, 4].text;
cvr_chgs_check = xlscvsheet.Cells[c, 5].text;
cvr_chgs_notes = xlscvsheet.Cells[c, 6].text;
if (string.IsNullOrEmpty(cvr_chgs_desc) == false)
dtchr.Rows.Add(sno, recid, cvr_chgs_desc, cvr_chgs_amt.AutoformatDecimal(), cvr_chgs_check, cvr_chgs_notes, cvr_type);
}
cvr_type = "r";
for (int c = 56; c <= 62; c++)
{
cvr_chgs_desc = xlscvsheet.Cells[c, 1].text;
cvr_chgs_amt = xlscvsheet.Cells[c, 4].text;
cvr_chgs_check = "";
cvr_chgs_notes = xlscvsheet.Cells[c, 5].text;
if (string.IsNullOrEmpty(cvr_chgs_desc) == false)
dtchr.Rows.Add(sno, recid, cvr_chgs_desc, cvr_chgs_amt.AutoformatDecimal(), cvr_chgs_check, cvr_chgs_notes, cvr_type);
}
#endregion
#endregion
#region write to db
MasterDataLayer.MasterData md = new MasterDataLayer.MasterData();
md.sqlbcopy(dt, "temp_engt");
md.sqlbcopy(dt1, "temp_schedule");
md.sqlbcopy(dtcvr, "temp_cvr_documents");
md.sqlbcopy(dtchr, "temp_cvr_charges");
string result;
using (StringWriter sw = new StringWriter())
{
dt.WriteXml(sw);
result = sw.ToString();
}
#endregion
}
catch (Exception ex)
{
Int32 lineno = new System.Diagnostics.StackTrace(ex, true).GetFrame(1).GetFileLineNumber();
throw new Exception(ex.Message + " Line No.:<<" + lineno.ToString() + ">> " + path);
}
finally
{
xlWorkbook.Save();
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
Kill_Excel_Process();
xlApp = null;
}
}
private void boutonExporter_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Fichier Excel (*.xlsx)|*.xlsx";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Exporter le fichier Excel vers...";
saveFileDialog.ShowDialog();
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Exported from gridview";
for(int i=1;i<dataGridView1.Columns.Count+1;i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;
}
for (int i=0; i < dataGridView1.Rows.Count ; i++)
{
for(int j=0;j<dataGridView1.Columns.Count;j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
workbook.SaveAs(saveFileDialog.FileName,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);
app.Quit();
}