private void ExportarDataGridViewExcel(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 ; 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();
}
}
private void btn_Excel_Click(object sender, EventArgs e)
{
if (dgv_Info.Rows.Count == 0)//判断是否有数据
return;//返回
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
excel.Application.Workbooks.Add(true);//在Excel中添加一个工作簿
excel.Visible = true;//设置Excel显示
//生成字段名称
for (int i = 0; i < dgv_Info.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv_Info.Columns[i].HeaderText;//将数据表格控件中的列表头填充到Excel中
}
//填充数据
for (int i = 0; i < dgv_Info.RowCount - 1; i++)//遍历数据表格控件的所有行
{
for (int j = 0; j < dgv_Info.ColumnCount; j++)//遍历数据表格控件的所有列
{
if (dgv_Info[j, i].ValueType == typeof(string))//判断遍历到的数据是否是字符串类型
{
excel.Cells[i + 2, j + 1] = "'" + dgv_Info[j, i].Value.ToString();//填充Excel表格
}
else
{
excel.Cells[i + 2, j + 1] = dgv_Info[j, i].Value.ToString();//填充Excel表格
}
}
}
}
/*
* The method getTable opens a file, gets the first worksheet in the workbook, and fills the datatable Table with its contents.
*/
public void setup()
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
openFile.Filter = "Excel Workbook|*.xls";
if (openFile.ShowDialog() == true)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
wb = app.Workbooks.Open(openFile.FileName);
Microsoft.Office.Interop.Excel.Worksheet ws = wb.Sheets.get_Item(1);
if (wb != null) // If there is a worksheet
{
if (ws.UsedRange != null) // if the worksheet is not empty
{
Table = toDataTable(ws);
}
}
app.Visible = true;
}
}
void CreateExcelFile()
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Add();
excel.Visible = true;
excel.DisplayAlerts = false;
excel.Range["A1"].Value = bankDataTable.Columns[0].ColumnName;
excel.Range["B1"].Value = bankDataTable.Columns[1].ColumnName;
excel.Range["C1"].Value = bankDataTable.Columns[2].ColumnName;
excel.Range["D1"].Value = bankDataTable.Columns[3].ColumnName;
excel.Range["E1"].Value = bankDataTable.Columns[4].ColumnName;
excel.Range["F1"].Value = bankDataTable.Columns[5].ColumnName;
// set date and time format for column F
excel.Range["F1", "F99"].NumberFormat = "M/D/YYYY H:MM AM/PM";
// set width for column F
excel.Range["F1"].EntireColumn.ColumnWidth = 17;
// shamelessly stolen from Terri
int j = 2;
foreach (DataRow row in bankDataTable.Rows)
{
int i = 65;
foreach( var item in row.ItemArray)
{
char c1 = (char)i++;
string cell = c1 + j.ToString();
excel.Range[cell].Value = item.ToString();
}
j++;
}
}
private void btn_Gather_Click(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value;//定义object缺省值
string[] P_str_Names = txt_MultiExcel.Text.Split(',');//存储所有选择的Excel文件名
string P_str_Name = "";//存储遍历到的Excel文件名
List<string> P_list_SheetNames = new List<string>();//实例化泛型集合对象，用来存储工作表名称
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
for (int i = 0; i < P_str_Names.Length - 1; i++)//遍历所有选择的Excel文件名
{
P_str_Name = P_str_Names[i];//记录遍历到的Excel文件名
//指定要复制的工作簿
Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名
for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表
{
//指定要复制的工作表
Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets[P_list_SheetNames[j]];//创建新工作表
TempWorksheet.Copy(missing, newWorksheet);//将工作表内容复制到目标工作表中
}
Tempworkbook.Close(false, missing, missing);//关闭临时工作簿
}
workbook.Save();//保存目标工作簿
workbook.Close(false, missing, missing);//关闭目标工作簿
MessageBox.Show("已经将所有选择的Excel工作表汇总到了一个Excel工作表中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
CloseProcess("EXCEL");//关闭所有Excel进程
}
private void importButton_Click(object sender, RoutedEventArgs e)
{
var app = new Microsoft.Office.Interop.Excel.Application();
var workbook = app.Workbooks.Add();
var worksheet = workbook.Worksheets[1];
worksheet.Cells[1, 1] = "Ім'я";
worksheet.Cells[1, 2] = "Дата і час";
worksheet.Cells[1, 3] = "Кількість помилок";
worksheet.Cells[1, 4] = "Витрачений час";
worksheet.Cells[1, 5] = "Результат";
using (var context = new Model.DB())
{
var results = context.ConfusedLinesTestResults.ToList();
for (int i = 0; i < results.Count(); i++)
{
worksheet.Cells[i + 2, 1] = results[i].Name;
worksheet.Cells[i + 2, 2] = results[i].Date;
worksheet.Cells[i + 2, 3] = results[i].ErrorsCount;
worksheet.Cells[i + 2, 4] = results[i].Time;
worksheet.Cells[i + 2, 5] = results[i].Result;
}
}
app.Visible = true;
}
public static void Init()
{
lock (Lock)
{
_excelApp = new Microsoft.Office.Interop.Excel.Application();
}
}
public static void print(DataGridView dataGridView1,string tableName)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();//Создание объекта Excel
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 15;
ExcelApp.Cells[1, 1] = tableName;//Передаём имя таблицы
ExcelApp.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
for (int i = 1; i < dataGridView1.Columns.Count; i++)//Заполняем названия столбцов
{
ExcelApp.Cells[2, i] = dataGridView1.Columns[i].HeaderText;
ExcelApp.Cells[2, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
}
for (int i = 1; i < dataGridView1.ColumnCount; i++)//Заполняем таблицу
{
for (int j = 0; j < dataGridView1.RowCount; j++)
{
try
{
ExcelApp.Cells[j + 3, i] = (dataGridView1[i, j].Value).ToString();
ExcelApp.Cells[j + 3, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
}
catch { }
}
}
ExcelApp.Visible = true;//Открываем Excel
}
public void exportarDatosExcel()
{
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[1];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Receive Payment";
//Se le manda a la cabezera los datos de las tabla
worksheet.Cells[1, 1] = "Date";
worksheet.Cells[1, 2] = "Num-Referent";
worksheet.Cells[1, 3] = "Receive From";
worksheet.Cells[1, 4] = "Amount";
for (int i = 0; i < tblReceive.Items.Count; i++)
{
ReceivePayment rpaymentcheck = (ReceivePayment)tblReceive.Items.GetItemAt(i);
worksheet.Cells[i + 3, 1] = rpaymentcheck.Date;
worksheet.Cells[i + 3, 2] = rpaymentcheck.RefNumber;
worksheet.Cells[i + 3, 3] = rpaymentcheck.Customer;
worksheet.Cells[i + 3, 4] = rpaymentcheck.Amount;
}
}
static void DisplayInExcel(IEnumerable<dynamic> entities)
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = true;
excelApp.Workbooks.Add();
dynamic workSheet = excelApp.ActiveSheet;
workSheet.Cells[1, "A"] = "Header A";
workSheet.Cells[1, "B"] = "Header B";
var row = 1;
foreach (var item in entities)
{
row++;
workSheet.Cells[row, "A"] = item.ColumnA;
workSheet.Cells[row, "B"] = item.ColumnB;
}
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
}
public static bool DataTableExportAsExcel(DataTable dt, string FileFullPath)
{
if (!System.IO.Directory.Exists(Path.GetDirectoryName(FileFullPath)))
{
CreatePath(FileFullPath);
}
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.EnableEvents = false;
excelApplication.Application.DisplayAlerts = false;
excelApplication.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelApplication.ActiveSheet;
excelApplication.Visible = false;
int nRowIndex = 0;
int nColumnIndex = 0;
int ColumnCount = dt.Columns.Count;
int RowCount = dt.Rows.Count;
object[,] strArr = new object[RowCount + 1, ColumnCount];
foreach (DataColumn col in dt.Columns)
{
strArr[nRowIndex, nColumnIndex] = col.ColumnName;
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < ColumnCount; i++)
{
strArr[nRowIndex, nColumnIndex] = row[i].ToString();
++nColumnIndex;
}
++nRowIndex;
nColumnIndex = 0;
}
string strExcelMaxColumnIndex = GetExcelMaxColumnIndex(ColumnCount, RowCount + 1);
Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.get_Range("A1", strExcelMaxColumnIndex);
myRange.get_Resize(RowCount + 1, ColumnCount);
try
{
myRange.Value2 = strArr;
myRange.Columns.AutoFit();
myRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
myWorkSheet.SaveAs(FileFullPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
return false;
}
excelApplication.Quit();
killexcel(excelApplication);
GC.Collect();
return true;
}
private void btn_Read_Click(object sender, EventArgs e)
{
int P_int_Count = 0;//记录正在读取的行数
string P_str_Line, P_str_Content = "";//记录读取行的内容及遍历到的内容
List<string> P_str_List = new List<string>();//存储读取的所有内容
StreamReader SReader = new StreamReader(txt_Txt.Text, Encoding.Default);//实例化流读取对象
while ((P_str_Line = SReader.ReadLine()) != null)//循环读取文本文件中的每一行
{
P_str_List.Add(P_str_Line);//将读取到的行内容添加到泛型集合中
P_int_Count++;//使当前读取行数加1
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
object missing = System.Reflection.Missing.Value;//获取缺少的object类型值
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet;//声明工作表对象
for (int i = 0; i < P_str_List.Count; i++)//遍历泛型集合
{
P_str_Content = P_str_List[i];//记录遍历到的值
newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
newWorksheet.Cells[1, 1] = P_str_Content;//直接将遍历到的内容添加到工作表中
}
excel.Application.DisplayAlerts = false;//不显示提示对话框
workbook.Save();//保存工作表
workbook.Close(false, missing, missing);//关闭工作表
MessageBox.Show("已经将文本文件的内容分解到了Excel的不同数据表中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public void exportExcel(DataGridView table)
{
if (table.Columns.Count > 0)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
int _columns = 0;
int _rows = 1;
foreach (DataGridViewColumn column in table.Columns)
{
excel.Cells[1, ++_columns] = column.Name;
}
foreach (DataGridViewRow row in table.Rows)
{
_columns = 0;
_rows++;
foreach (DataGridViewColumn column in table.Columns)
{
excel.Cells[_rows, ++_columns] = row.Cells[column.Name].Value;
}
}
excel.Visible = true;
}
}
public List<string> GetExcelSheets(string excelFileName)
{
Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"",
true,
false,
0,
true,
false,
false);
Microsoft.Office.Interop.Excel.Sheets sheets = workBookObject.Worksheets;
// get the first and only worksheet from the collection of worksheets
List<string> sheetNames = new List<string>();
Regex regSheetName = new Regex("Sheet\\d+");
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets) {
if (!regSheetName.IsMatch(sheet.Name)) {
sheetNames.Add(sheet.Name);
}
Marshal.ReleaseComObject(sheet);
}
excelFileObject.Quit();
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(workBookObject);
Marshal.ReleaseComObject(excelFileObject);
return sheetNames;
}
public ExcelWriter(string _fileName)
{
fileName = _fileName;
if(File.Exists(FilePath))
throw new ApplicationException("File already exists: " + FilePath);
File.Create(FilePath);
app = new Microsoft.Office.Interop.Excel.Application();
Console.Error.WriteLine("Connected to Excel");
wbs = app.Workbooks;
wb = wbs.Add(1);
wb.Activate();
wss = wb.Sheets;
ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1);
Console.Error.WriteLine("Excel Worksheet Initialized");
}
public void Print(YellowstonePathology.Business.Search.ReportSearchList caseList, string description, DateTime printDate)
{
Microsoft.Office.Interop.Excel.Application xlApp;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbook wb = xlApp.Workbooks.Add(@"\\CFileServer\documents\ReportTemplates\MolecularTesting\CaseList.xlt");
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
ws.Cells[3, 1] = "Batch: " + description + " - " + printDate.ToShortDateString();
int rowPosition = 6;
for (int i = caseList.Count - 1; i > -1; i--)
{
ws.Cells[rowPosition, 1] = caseList[i].ReportNo;
ws.Cells[rowPosition, 2] = caseList[i].PanelSetName;
ws.Cells[rowPosition, 3] = caseList[i].PatientName;
ws.Cells[rowPosition, 4] = caseList[i].PhysicianName + " - " + caseList[i].ClientName;
ws.Cells[rowPosition, 5] = caseList[i].OrderedBy;
rowPosition++;
}
Object oMissing = Type.Missing;
Object oFalse = false;
ws.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close(oFalse, oMissing, oMissing);
xlApp.Quit();
}
public void Dispose()
{
excelApp.Quit();
Marshal.ReleaseComObject(excelApp);
Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
}
public void insertBTN(String query, String path)
{
sqlCon.ConnectionString = conn;
sqlCon.Open();
SqlDataAdapter da = new SqlDataAdapter(query, sqlCon);
System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
da.Fill(dtMainSQLData);
DataColumnCollection dcCollection = dtMainSQLData.Columns;
// Export Data into EXCEL Sheet
Microsoft.Office.Interop.Excel.Application ExcelApp = new
Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
// ExcelApp.Cells.CopyFromRecordset(objRS);
for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
{
for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
{
if (i == 1)
{
ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
}
else
ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs(path + "\\Results.xlsx");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
private void btn_Read_Click(object sender, EventArgs e)
{
int P_int_Count=0;//记录正在读取的行数
string P_str_Line, P_str_Content = "";//记录读取行的内容及遍历到的内容
List<string> P_str_List = new List<string>();//存储读取的所有内容
StreamReader SReader = new StreamReader(txt_Txt.Text, Encoding.Default);//实例化流读取对象
while ((P_str_Line = SReader.ReadLine()) != null)//循环读取文本文件中的每一行
{
P_str_List.Add(P_str_Line);//将读取到的行内容添加到泛型集合中
P_int_Count++;//使当前读取行数加1
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
object missing = System.Reflection.Missing.Value;//获取缺少的object类型值
//打开指定的Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);
excel.Application.DisplayAlerts = false;//不显示提示对话框
for (int i = 0; i < P_str_List.Count; i++)//遍历泛型集合
{
P_str_Content = P_str_List[i];//记录遍历到的值
if (Regex.IsMatch(P_str_Content, "^[0-9]*[1-9][0-9]*$"))//判断是否是数字
newWorksheet.Cells[i + 1, 1] = Convert.ToDecimal(P_str_Content).ToString("￥00.00");//格式化为货币格式，再添加到工作表中
else
newWorksheet.Cells[i + 1, 1] = P_str_Content;//直接将遍历到的内容添加到工作表中
}
workbook.Save();//保存工作表
workbook.Close(false, missing, missing);//关闭工作表
MessageBox.Show("已经将文本文件内容成功导入Excel工作表中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public void ConvertCsvToExcel_MicrosoftOfficeInteropExcel()
{
StringBuilder content = new StringBuilder();
content.AppendLine("param1\tparam2\tstatus");
content.AppendLine("0.5\t10\tpassed");
content.AppendLine("10\t20\tfail");
using (TemporaryFile xlsxFile = new TemporaryFile(".xlsx"))
{
Clipboard.SetText(content.ToString());
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Microsoft.Office.Interop.Excel.Application();
// for excel visibility
//xlexcel.Visible = true;
// Creating a new workbook
xlWorkBook = xlexcel.Workbooks.Add(misValue);
// Putting Sheet 1 as the sheet you want to put the data within
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
// creating the range
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range) xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.Paste(CR, false);
xlWorkSheet.SaveAs(xlsxFile.FileName);
xlexcel.Quit();
Console.WriteLine("Created file {0}", xlsxFile.FileName);
}
}
public static void OpenExcelDocs2(string filename, double[] content)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //引用Excel工作簿
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.get_Item(1); ; //引用Excel工作页面
excel.Visible = false;
sheet.Cells[24, 3] = content[1];
sheet.Cells[25, 3] = content[0];
book.Save();
book.Close(Type.Missing, Type.Missing, Type.Missing);
excel.Quit(); //应用程序推出，但是进程还在运行
IntPtr t = new IntPtr(excel.Hwnd); //杀死进程的好方法，很有效
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
//sheet = null;
//book = null;
//excel = null; //不能杀死进程
//System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); //可以释放对象，但是不能杀死进程
//System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
/// <summary>
/// 加载数据同时保存数据到指定位置
/// </summary>
/// <param name="obj"></param>
private void AddData(FarPoint.Win.Spread.FpSpread obj)
{
wait = new WaitDialogForm("", "正在加载数据, 请稍候...");
try
{
//打开Excel表格
//清空工作表
fpSpread1.Sheets.Clear();
obj.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls");
PF.SpreadRemoveEmptyCells(obj);
//this.AddCellChanged();
//this.barEditItem2.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
//S4_2_1.AddBarEditItems(this.barEditItem2, this.barEditItem1, this);
}
catch (System.Exception e)
{
//如果打开出错则重新生成并保存
LoadData();
//判断文件夹是否存在，不存在则创建
if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls"))
{
Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls");
}
//保存EXcel文件
obj.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet);
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls";
workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//取消保护工作表
ws.Unprotect(Missing.Value);
//有数据的行数
int row = ws.UsedRange.Rows.Count;
//有数据的列数
int col = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//设区域内的单元格自动换行
range.WrapText = true;
//保护工作表
ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//保存工作簿
workBook.Save();
//关闭工作簿
excelApp.Workbooks.Close();
}
wait.Close();
}
public static void Main(string[] args)
{
PowerMILL.Application pMAppliacation = (PowerMILL.Application) System.Runtime.InteropServices.Marshal.GetActiveObject("PowerMill.Application");
Microsoft.Office.Interop.Excel.Application exApplication = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook exWorkbook = exApplication.Workbooks.Open(@"e:\ExTest.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t",false, false, 0, true, 1, 0);
Microsoft.Office.Interop.Excel._Worksheet exWorksheet = (Microsoft.Office.Interop.Excel._Worksheet)exWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range exRange = exWorksheet.UsedRange;
int rowCount = exRange.Rows.Count;
int colCount = exRange.Columns.Count;
for (int i = 1; i <= rowCount; i++) {
for (int j = 1; j <= colCount; j++) {
string valueForPM = null;
try {
valueForPM = (string)(exRange.Cells[i, j] as Microsoft.Office.Interop.Excel.Range).Value.ToString();
} catch {}
if (valueForPM != null) {
pMAppliacation.DoCommand(@"MESSAGE INFO """+ valueForPM +@"""");
}
}
}
}
public byte[] Convert()
{
var excel = new Application();
if (File.Exists(FileToSave))
{
File.Delete(FileToSave);
}
try
{
excel.Workbooks.Open(Filename: FullFilePath);
excel.Visible = false;
if (excel.Workbooks.Count > 0)
{
var wsEnumerator = excel.ActiveWorkbook.Worksheets.GetEnumerator();
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
while (wsEnumerator.MoveNext())
{
var wsCurrent = (Microsoft.Office.Interop.Excel.Worksheet)wsEnumerator.Current;
wsCurrent.SaveAs(Filename: FileToSave, FileFormat: format);
break;
}
excel.Workbooks.Close();
}
}
finally
{
excel.Application.Quit();
}
return base.ReadConvertedFile();
}
public void CreateExcelFile()
{
app = new Microsoft.Office.Interop.Excel.Application();
workbook = app.Workbooks.Add(1);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
AddExcelProcessTolist();
}
private void button2_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
////Книга.
//ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
////Таблица.
//ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelApp.Workbooks.Open(Application.StartupPath + "\\result.xlsx");
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
ExcelApp.Cells[3, j + 1] = dataGridView1.Columns[j].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
ExcelApp.Cells[i + 4, j + 1] = dataGridView1.Rows[i].Cells[j].Value;
}
}
//Вызываем нашу созданную эксельку.
ExcelApp.Visible = true;
ExcelApp.UserControl = true;
}
private void btn_Export_Click(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value;//声明object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(txt_Path.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet;//声明工作表
Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application();//实例化Access对象
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[cbox_SheetName.Text]);//获取选择的工作表
worksheet.Move(workbook.Sheets[1], missing);//将选择的工作表作为第一个工作表
object P_obj_Name = (object)worksheet.Name;//获取工作表名称
excel.DisplayAlerts = false;//设置Excel保存时不显示对话框
workbook.Save();//保存工作簿
CloseProcess("EXCEL");//关闭所有Excel进程
object P_obj_Excel = (object)txt_Path.Text;//记录Excel文件路径
try
{
access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
//将Excel指定工作表中的数据导入到Access中
access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_obj_Excel, true, missing, missing);
access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);//关闭并保存Access数据库文件
CloseProcess("MSACCESS");//关闭所有Access数据库进程
MessageBox.Show("已经将Excel的" + cbox_SheetName.Text + "工作表中的数据导入到Access数据库中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("Access数据库中已经存在该表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public ExcelFile(string filePath)
{
_application = new Microsoft.Office.Interop.Excel.Application();
_workbook = _application.Workbooks.Open(filePath);
_worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets.Item[1];
_filename = filePath;
_currentSheetNumber = 1;
}
/*
private void loadKeyTable(Microsoft.Office.Interop.Excel.Worksheet sheet)
{
foreach (string key in keyList_)
{
for (int i = 1; i < 100; i++)
{
Microsoft.Office.Interop.Excel.Range rgn = sheet.Cells[3, i];
dynamic val = rgn.Value2;
string title = Convert.ToString(val);
int indexof = title.IndexOf(key);
if (indexof != -1)
{
keyTable_.Add(key, i);
break;
}
}
}
}
*/
public DataExcelManager(Microsoft.Office.Interop.Excel.Application app, string path, string sheet)
{
excelApp_ = app;
excelPath_ = path;
sheetName_ = sheet;
//keyList_ = new string[] { "Collection Retail", "Market", "Product reference", "Product Reference Name", "Product color code", "Product color", "Product size", "YEN", "Note" };
//keyTable_ = new Hashtable();
}
//导出
private void barBtnDaochuExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
string fname = "";
saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
fname = saveFileDialog1.FileName;
try
{
fpSpread1.SaveExcel(fname);
//以下是打开文件设表格自动换行
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws = null;
Microsoft.Office.Interop.Excel.Range range = null;
excelApp = new Microsoft.Office.Interop.Excel.Application();
workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
for (int i = 1; i <= workBook.Worksheets.Count; i++)
{
ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
//取消保护工作表
ws.Unprotect(Missing.Value);
//有数据的行数
int row = ws.UsedRange.Rows.Count;
//有数据的列数
int col = ws.UsedRange.Columns.Count;
//创建一个区域
range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
//设区域内的单元格自动换行
range.WrapText = true;
//保护工作表
//ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
//保存工作簿
workBook.Save();
//关闭工作簿
excelApp.Workbooks.Close();
if (MessageBox.Show("导出成功，是否打开该文档？","询问",MessageBoxButtons.YesNo,MessageBoxIcon.Question) != DialogResult.Yes)
return;
System.Diagnostics.Process.Start(fname);
}
catch
{
MessageBox.Show("无法保存" + fname + "。请用其他文件名保存文件，或将文件存至其他位置。");
return;
}
}
}
private void OutputExecelFromDataTable(DataTable dt, string EXCEL_SAVE_PATH)
{
if (dt.Rows.Count > 0)
{// Excelを参照設定する必要があります
// [参照の追加],[COM],[Microsoft Excel *.* Object Library]
// Imports Microsoft.Office.Interop (必要)
// Imports System.Runtime.InteropServices (必要)
//Excel出力
// EXCEL関連オブジェクトの定義
// Excelを起動する
Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
objExcel.Visible = true;
try
{
//待機状態
Cursor.Current = Cursors.WaitCursor;
// ブック（ファイル）を開く
Microsoft.Office.Interop.Excel.Workbook objWorkBook = objExcel.Workbooks.Open(EXCEL_SAVE_PATH);
try
{
// 現在日時を取得
string timestanpText = DateTime.Now.ToString(" yyyy/MM/dd HH:mm"); //bbl.GetDate();
int count = 0;
int currentRow = (int)ERow.DetailStartRow;
int page = 1;
string breakKey = "";
foreach (DataRow row in dt.Rows)
{
if (breakKey != "" && breakKey != row["VendorCD"].ToString())
{
count = DATA_COUNT;
}
if (count >= DATA_COUNT)
{
//行１からGYO_COUNTまでの行をこぴーする
objWorkBook.Sheets[1].Range("1:" + GYO_COUNT).Copy(objWorkBook.Sheets[1].Range("A" + (GYO_COUNT * page + 1)));
//Detail部をクリアする
currentRow = (int)ERow.DetailStartRow + page * GYO_COUNT;
objWorkBook.Sheets[1].Range("A" + currentRow + ":BZ" + (currentRow + DATA_COUNT - 1)).ClearContents();
page++;
count = 0;
}
if (count == 0)
{
breakKey = row["VendorCD"].ToString();
if (page == 1)
{
objWorkBook.Sheets[1].Cells[2, 64] = timestanpText;
objWorkBook.Sheets[1].Cells[(int)ERow.Print1, (int)ECell.Print1] = row["Print1"].ToString();
objWorkBook.Sheets[1].Cells[(int)ERow.Print2, (int)ECell.Print2] = row["Print2"].ToString();
objWorkBook.Sheets[1].Cells[(int)ERow.Print3, (int)ECell.Print3] = row["Print3"].ToString();
objWorkBook.Sheets[1].Cells[(int)ERow.Print4, (int)ECell.Print4] = row["Print4"].ToString();
objWorkBook.Sheets[1].Cells[(int)ERow.ZIP, (int)ECell.ZIP] = row["ZIP"].ToString();
objWorkBook.Sheets[1].Cells[(int)ERow.CompanyName, (int)ECell.CompanyName] = row["CompanyName"].ToString();
objWorkBook.Sheets[1].Cells[(int)ERow.TEL, (int)ECell.TEL] = row["TEL"].ToString();
}
objWorkBook.Sheets[1].Cells[(int)ERow.VendorName + (page - 1) * GYO_COUNT, (int)ECell.VendorName] = row["VendorName"].ToString();
objWorkBook.Sheets[1].Cells[(int)ERow.OrderPerson + (page - 1) * GYO_COUNT, (int)ECell.OrderPerson] = row["OrderPerson"].ToString();
}
objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.PurchaseDate] = row["PurchaseDate"].ToString();
objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.VendorDeliveryNo] = row["VendorDeliveryNo"].ToString();
objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.MakerItem] = row["MakerItem"].ToString();
objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.ColorName] = row["ColorName"].ToString() + Environment.NewLine + row["SizeName"].ToString();
objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.PurchaserUnitPrice] = row["PurchaserUnitPrice"].ToString();
objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.OrderUnitPrice] = row["OrderUnitPrice"].ToString();
objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.PurchaseSu] = row["PurchaseSu"].ToString();
currentRow++;
count++;
}
//// エクセル表示
//objExcel.Visible = true;
objWorkBook.Save();
// クローズ
objWorkBook.Close(false);
objExcel.Quit();
}
finally
{
if (objWorkBook != null)
{
try
{
//objWorkBook.Close();
}
finally
{
// EXCEL解放
System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkBook);
objWorkBook = null /* TODO Change to default(_) if this is not a reference type */;
}
}
}
}
finally
{
if (objExcel != null)
{
try
{
//objExcel.Quit();
}
finally
{
// EXCEL解放
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel);
objExcel = null /* TODO Change to default(_) if this is not a reference type */;
}
}
//元に戻す
Cursor.Current = Cursors.Default;
}
}
}
/// <summary>
/// FUNCTION FOR EXPORT TO EXCEL
/// </summary>
/// <param name="dataTable"></param>
/// <param name="worksheetName"></param>
/// <param name="saveAsLocation"></param>
/// <returns></returns>
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Workk sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
excelSheet.Cells[1, 1] = ReporType;
excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();
// loop through each row and add values to our sheet
int rowcount = 2;
foreach (DataRow datarow in dataTable.Rows)
{
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
// on the first iteration we add the column headers
if (rowcount == 3)
{
excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();
//for alternate rows
if (rowcount > 3)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
excelworkBook.SaveAs(saveAsLocation);
excelworkBook.Close();
excel.Quit();
ShowSavedExcelFile(saveAsLocation);
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
private void expExportToExcel_Expanded(object sender, RoutedEventArgs e)
{
int intRowCounter;
int intRowNumberOfRecords;
int intColumnCounter;
int intColumnNumberOfRecords;
// 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
{
expExportToExcel.IsExpanded = false;
worksheet = workbook.ActiveSheet;
worksheet.Name = "OpenOrders";
int cellRowIndex = 1;
int cellColumnIndex = 1;
intRowNumberOfRecords = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Rows.Count;
intColumnNumberOfRecords = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Columns.Count;
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Columns[intColumnCounter].ColumnName;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
{
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Rows[intRowCounter][intColumnCounter].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 = 1;
saveDialog.ShowDialog();
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
catch (System.Exception ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // My Tickets // Export To Excel " + ex.Message);
MessageBox.Show(ex.ToString());
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
static void TaskMethod(object title)
{
lock (taskMethodLocj)
{
DataGridView dataGridView1 = InDataWindow.InData_.dataGridView1;
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 < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i == 7)
{
worksheet.Cells[r + 2, i + 1] = "'" + dataGridView1.Rows[r].Cells[i].Value;
}
else
{
worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
}
}
Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应
// if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
// {
// Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
// rg.NumberFormat = "00000000";
// }
if (title.ToString() != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(title.ToString());
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect(); //强行销毁
// if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
MessageBox.Show("导出文件成功", "提示", MessageBoxButtons.OK);
}
}
private void btn_exportExcel_Click(object sender, EventArgs e)
{
btn_exportExcel.Enabled = false;
if (grdvw_SN_data.Rows.Count > 1)
{
// 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
Microsoft.Office.Interop.Excel.Range aRange;
//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";
//worksheet.AllocatedRange.AutoFitColumns();
//worksheet.AllocatedRange.AutoFitRows();
// storing header part in Excel
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "XLS (*.xlsx)|*.xlsx";
sfd.FileName = "Output.xlsx";
//string filename1 = @"D:\temp\test.csv";
bool fileError = false;
if (sfd.ShowDialog() == DialogResult.OK)
{
if (File.Exists(sfd.FileName))
{
try
{
File.Delete(sfd.FileName);
}
catch (IOException ex)
{
fileError = true;
MessageBox.Show("It wasn't possible to write the data to the disk." + ex.Message);
}
}
}
if (!fileError)
{
try
{
string CsvFpath = sfd.FileName;
for (int l = 1; l < grdvw_SN_data.Columns.Count + 1; l++)
{
worksheet.Cells[1, l] = grdvw_SN_data.Columns[l - 1].HeaderText;
}
int i = 0, j = 0;
// storing Each row and column value to excel sheet
for (i = 0; i < grdvw_SN_data.Rows.Count - 1; i++)
{
for (j = 0; j < grdvw_SN_data.Columns.Count; j++)
{
//app.Range["G"].NumberFormat =
app.Range["A" + (i + 2)].NumberFormat = "@";
app.Range["E" + (i + 2)].NumberFormat = "@";
app.Range["F" + (i + 2)].NumberFormat = "0";
app.Range["G" + (i + 2)].NumberFormat = "@";
app.Range["J" + (i + 2)].NumberFormat = "0";
app.Range["K" + (i + 2)].NumberFormat = "@";
worksheet.Cells[i + 2, j + 1] = grdvw_SN_data.Rows[i].Cells[j].Value.ToString();
}
}
aRange = worksheet.get_Range("A2", "N" + (i + 1));
aRange.Columns.AutoFit();
// CellRange cRange = worksheet.Range[]
aRange.Borders.LineStyle = LineStyleType.Dashed;
//aRange.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
//aRange.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
// save the application
workbook.SaveAs(CsvFpath, 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
MessageBox.Show("Data Exported Successfully !!!", "Info");
app.Quit();
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.Message);
}
}
}
else
{
MessageBox.Show("No Record To Export !!!", "Info");
}
btn_exportExcel.Enabled = true;
}
private bool ExportDataToExcel()
{
// 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(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
try
{
// 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 = cmbBillingCycle.Text;
// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value == null ? string.Empty : dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
var outputPath = Properties.Settings.Default.OutputFolderPath;
outputPath += cmbAccountName.Text + "\\" + cmbYear.Text + "\\" + cmbBillingCycle.Text + "\\";
var path = string.Empty;
if (errorMsgs.Count() > 0)
{
outputPath = outputPath + "ErrorReport\\";
CreateFolderStructure(outputPath);
path = outputPath + "Error.xls";
}
else
{
CreateFolderStructure(outputPath);
path = outputPath + cmbBillingCycle.Text + ".xls";
}
// save the application
workbook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//workbook.Close();
workbook.Close(0);
//app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
// Exit from the application
return(true);
}
catch
{
return(false);
}
finally
{
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
app.Quit();
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
}
//app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
}
public ActionResult UploadFileAjax(string accion, string fec_informe, string fec_informe_hasta)
{
try
{
for (int i = 0; i < Request.Files.Count; i++)
{
var fileContent = Request.Files[i];
if (fileContent != null && fileContent.ContentLength > 0)
{
string fileCliente = Path.GetFileName(fileContent.FileName);
string fileExtension = Path.GetExtension(fileContent.FileName);
string fileDestino = string.Empty;
string fileDestinoDBS = string.Empty;
int pid = 0;
if (fileExtension != ".xlsx" && fileExtension != ".xls")
{
return(Json(
new Response
{
Status = HttpStatusCode.BadRequest,
Message = "El archivo debe ser tipo Excel (.xlsx,.xls)",
},
JsonRequestBehavior.AllowGet));
}
// get a stream
var stream = fileContent.InputStream;
var nomenclatura = "BAL_Car_" + DateTime.Now.ToString("yyMMddhhmmss") + ".xlsx";
if (HttpContext.IsDebuggingEnabled)
{
fileDestino = Path.Combine(@"M:\MVC\com\xlsd", nomenclatura);
fileDestinoDBS = fileDestino.Replace(@"M:\MVC\com\xlsd", xlsPathDES);
}
else
{
fileDestino = Path.Combine(Server.MapPath("~/XlsD"), nomenclatura);
fileDestinoDBS = fileDestino.Replace(Server.MapPath("~/XlsD"), xlsPathPRO);
}
var mensajeError = string.Empty;
if (accion == "CARGA")
{
using (var fileStream = System.IO.File.Create(fileDestinoDBS))
{
stream.CopyTo(fileStream);
}
}
// Formateando excel a 2 decimales
//Grabacion de temporal en sql
var cod_unidad_negocio = string.Empty;
if (Session["cod_unidad_negocio"] != null)
{
cod_unidad_negocio = Session["cod_unidad_negocio"].ToString();
}
try
{
var excelApp = new Microsoft.Office.Interop.Excel.Application(); //CreateObject("Excel.Application")
excelApp.Application.Visible = false;
excelApp.DisplayAlerts = false;
var excelWorkbook = excelApp.Workbooks.Open(Filename: fileDestinoDBS, ReadOnly: false);
GetWindowThreadProcessId(excelApp.Hwnd, out pid);
try
{
excelWorkbook.Sheets["BD Balmet"].Range("F6:EO500").NumberFormat = "0.0000000000000000";
}
catch (Exception exc)
{
mensajeError = "ERROR: En la ejecución de Excel " + exc.Message;
}
//Close Excel
excelWorkbook.Close(SaveChanges: true);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
var archivoDestino = fileDestinoDBS.Replace(@"\", "/");
try
{
//procesoExcel.Kill();
Process.GetProcessById(pid).Kill();
}
catch (Exception)
{
}
}
catch (Exception ex)
{
System.Threading.Thread.Sleep(1000); //Espera un segundo para intentarlo de nuevo
mensajeError += " Servicio de Excel en estos momentos se encuentra sin recursos.\r Por favor intentelo mas tarde.";
}
DateTime dtfecInforme = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", CultureInfo.InvariantCulture);
DateTime dtfecInformeHasta = DateTime.ParseExact(fec_informe_hasta, "dd/MM/yyyy", CultureInfo.InvariantCulture);
var execute = balNeg.up_bal_pro_cargaXLS(cod_unidad_negocio, Usuario.Item1.cod_usuario, fileDestinoDBS, fileDestino, "CARGA", 0, dtfecInforme, dtfecInformeHasta);
if (execute.tipo == "ERROR")
{
return(Json(
new Response
{
Status = HttpStatusCode.BadRequest,
Message = execute.mensaje.Replace("\n", "<br>")
},
JsonRequestBehavior.AllowGet));
}
else if (execute.tipo == "SUCCESS")
{
execute = balNeg.up_bal_pro_cargaXLS(cod_unidad_negocio, Usuario.Item1.cod_usuario, fileDestinoDBS, fileDestino, "START", 0, dtfecInforme, dtfecInformeHasta);
if (execute.tipo == "ERROR")
{
return(Json(
new Response
{
Status = HttpStatusCode.BadRequest,
Message = execute.mensaje,
Kind = execute.tipo,
Id = execute.Id.ToString()
},
JsonRequestBehavior.AllowGet));
}
else if (execute.tipo == "WARNING")
{
return(Json(
new Response
{
Status = HttpStatusCode.BadRequest,
Message = execute.mensaje,
Kind = execute.tipo,
Id = execute.Id.ToString(),
Aux = nomenclatura,
Aux2 = fileDestinoDBS,
Aux3 = fileDestino
},
JsonRequestBehavior.AllowGet));
}
else
{
execute = balNeg.up_bal_pro_cargaXLS(cod_unidad_negocio, Usuario.Item1.cod_usuario, fileDestinoDBS, fileDestino, "DETALLE", execute.Id, dtfecInforme, dtfecInformeHasta);
if (execute.tipo == "ERROR")
{
return(Json(
new Response
{
Status = HttpStatusCode.BadRequest,
Message = execute.mensaje,
Kind = execute.tipo,
Id = execute.Id.ToString()
},
JsonRequestBehavior.AllowGet));
}
else
{
return(Json(
new Response
{
Status = HttpStatusCode.OK,
Message = execute.mensaje
},
JsonRequestBehavior.AllowGet));
}
}
}
}
}
}
catch (Exception e)
{
return(Json(
new Response
{
Status = HttpStatusCode.BadRequest,
Message = "El archivo no se cargó. " + e.Message,
},
JsonRequestBehavior.AllowGet));
}
return(Json(
new Response
{
Status = HttpStatusCode.BadRequest,
Message = "No se puede continuar por errores en el modelo",
Errors = ModelState.Values.SelectMany(x => x.Errors).Select(x => x.ErrorMessage)
},
JsonRequestBehavior.AllowGet));
}
public static void Run()
{
#region kill any Excel process before run the app
Process[] prs = Process.GetProcesses();
foreach (Process pr in prs)
{
if (pr.ProcessName == "EXCEL")
{
pr.Kill();
}
}
#endregion
try
{
// Create a new FileSystemWatcher and set its properties.
FileSystemWatcher watcher = new FileSystemWatcher();
watcher.Path = ConfigurationManager.AppSettings["RootFolder"];
/* Watch for changes in LastAccess and LastWrite times, and the renaming of files or directories. */
watcher.NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite
| NotifyFilters.FileName | NotifyFilters.DirectoryName;
// Only watch xls files.
watcher.Filter = "*.xls*";
// Add event handlers.
// Begin watching.
watcher.EnableRaisingEvents = true;
#region open and work with excel file
foreach (string file in Directory.EnumerateFiles(ConfigurationManager.AppSettings["RootFolder"], "*.xls"))
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(file);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
inputFileName = Path.GetFileNameWithoutExtension(file);
ArrayList vagonsList = new ArrayList();
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //create COM-object Excel
excel.Visible = false; //make excel process invisible
excel.Workbooks.Add(Type.Missing); //add workbook
excel.SheetsInNewWorkbook = 1; //number of pages in workbook
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks[1]; //get link on 1st book
Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Worksheets.get_Item(1); //get link on 1st page
sheet.Name = "Report" + " " + DateTime.Now.ToString("dd.MM.yy");
sheet.Cells[1, 1] = "№ КОНТЕЙНЕРА";
sheet.Cells[1, 2] = "СТАНЦ";
sheet.Cells[1, 3] = "ОПЕР";
sheet.Cells[1, 4] = "ДАТА";
sheet.Cells[1, 5] = "ВРЕМЯ";
sheet.Cells[1, 6] = "СОСТ";
sheet.Cells[1, 7] = "N ОТПР";
sheet.Cells[1, 8] = "N ВАГОНА";
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
if (j == 1)
{
if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
{
//add useful things here!
vagonsList.Add(xlRange.Cells[i, j].Value2.ToString());
}
}
}
}
ConsoleColor color = Console.ForegroundColor;
Console.ForegroundColor = ConsoleColor.DarkYellow;
Console.WriteLine($"Start processing file {inputFileName}.");
Console.ForegroundColor = color;
for (int i = 0; i < vagonsList.Count; i++)
{
try
{
int list = vagonsList.Count;
var container = vagonsList[i];
if (vagonsList != null)
{
//Console.WriteLine($"Start processing file {inputFileName}.");
File.WriteAllText(ConfigurationManager.AppSettings["querryFolder"] + "01" + 11 + "000" + ".000", string.Format("(:217 0:1680 {0}:)", container));
Console.WriteLine($"Now processing: {container}");
System.Threading.Thread.Sleep(9900);
}
else
{
break;
}
string answerFileName = "01" + 11 /*15й порт Левитан, 11 мой*/ + "2400";
DirectoryInfo dirConcentratorPath = new DirectoryInfo(ConfigurationManager.AppSettings["answerFolder"]);
FileInfo[] fileInDir = dirConcentratorPath.GetFiles(answerFileName + "*.*");
foreach (FileInfo foundFile in fileInDir)
{
string fullName = foundFile.FullName;
var lines = File.ReadAllLines(foundFile.FullName, Encoding.GetEncoding(866));
sheet.get_Range("E2", string.Format("E{0}", vagonsList.Count)).NumberFormat = "@";
string text = ""; // search key variable
using (StreamReader sr = new StreamReader(foundFile.FullName, Encoding.GetEncoding(866)))
{
text = sr.ReadToEnd();
Regex regexNoInfo = new Regex("[Н][Е][Т]\\s[А-Я]{10}");
foreach (var line in lines)
{
var NoInfoMatches = regexNoInfo.Matches(line);
if (NoInfoMatches.Count > 0)
{
var res = NoInfoMatches[0].Value;
string noinfo = "-";
sheet.Cells[i + 2, 1].Value = container;
sheet.Cells[i + 2, 2].Value = noinfo;
sheet.Cells[i + 2, 3].Value = noinfo;
sheet.Cells[i + 2, 4].Value = noinfo;
sheet.Cells[i + 2, 5].Value = noinfo;
sheet.Cells[i + 2, 6].Value = noinfo;
sheet.Cells[i + 2, 7].Value = noinfo;
sheet.Cells[i + 2, 8].Value = noinfo;
break;
}
else
{
continue;
}
}
Regex regexOkInfo = new Regex(@"([О][П][Е][Р][А][Ц][И][И]\s[С]\s[К])");
foreach (var line in lines)
{
var okInfoMatches = regexOkInfo.Matches(line);
if (okInfoMatches.Count > 0)
{
string tempAnswer = lines[lines.Length - 4];
string pattern = @"([A-Я]{4})|([А-Я]{3}.)|(\d\d[.]\d\d[.]\d\d)|([А-Я]{3}.)|(\d{2}[-]\d{2})|(\d{8})|(\d{6}?)";
Regex rgx = new Regex(pattern);
MatchCollection matchList = Regex.Matches(tempAnswer, pattern);
var results = matchList.Cast <Match>().Select(match => match.Value).ToList();
var station = matchList[0].Value;
var operation = matchList[1].Value;
var dt = matchList[2].Value;
DateTime dateOfOperation = Convert.ToDateTime(dt);
string timeOfOperation = matchList[3].Value;
var state = matchList[4].Value;
if (results.Count == 5)
{
sheet.Cells[i + 2, 1].Value = container; //get ontainer name in column
sheet.Cells[i + 2, 2].Value = station;
sheet.Cells[i + 2, 3].Value = operation;
sheet.Cells[i + 2, 4].Value = dateOfOperation;
sheet.Cells[i + 2, 5].Value = timeOfOperation;
sheet.Cells[i + 2, 6].Value = state;
string noinfo = "-";
sheet.Cells[i + 2, 7].Value = noinfo;
sheet.Cells[i + 2, 8].Value = noinfo;
}
else if (results.Count == 6)
{
var otpravka = matchList[5].Value;
sheet.Cells[i + 2, 1].Value = container; //get ontainer name in column
sheet.Cells[i + 2, 2].Value = station;
sheet.Cells[i + 2, 3].Value = operation;
sheet.Cells[i + 2, 4].Value = dateOfOperation;
sheet.Cells[i + 2, 5].Value = timeOfOperation;
sheet.Cells[i + 2, 6].Value = state;
sheet.Cells[i + 2, 7].Value = otpravka;
string noinfo = "-";
sheet.Cells[i + 2, 8].Value = noinfo;
}
else
{
var otpravka = matchList[5].Value;
var vagon = matchList[6].Value;
#endregion
sheet.Cells[i + 2, 1].Value = container; //get ontainer name in column
sheet.Cells[i + 2, 2].Value = station;
sheet.Cells[i + 2, 3].Value = operation;
sheet.Cells[i + 2, 4].Value = dateOfOperation;
sheet.Cells[i + 2, 5].Value = timeOfOperation;
sheet.Cells[i + 2, 6].Value = state;
sheet.Cells[i + 2, 7].Value = otpravka;
sheet.Cells[i + 2, 8].Value = vagon;
}
}
else
{
continue;
}
}
}
File.Delete(foundFile.FullName);
}
}
catch (Exception e)
{
foreach (Process pr in prs)
{
if (pr.ProcessName == "EXCEL" && pr.ProcessName == "FastCustom")
{
pr.Kill();
}
}
//this will use the "fileLogger" logger from our NLog.config file
Logger logger = LogManager.GetLogger("fileLogger");
//add custom message and pass in the exception
logger.Error(e, "Need fix some problems!");
}
}
#region Formatting excel
var formatTable = vagonsList.Count + 1; // fix applying formatting for last string
sheet.get_Range("B1", string.Format("H{0}", formatTable)).Cells.HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //aligment center
Microsoft.Office.Interop.Excel.Range chartRange;
chartRange = sheet.get_Range("a1", "h1");
foreach (Microsoft.Office.Interop.Excel.Range cells in chartRange.Cells)
{
cells.BorderAround2();
}
#endregion
try
{
sheet.Columns.AutoFit(); // autofit
string reportName = inputFileName + " от " + DateTime.Now.ToString("dd.MM.yyyy, HH-mm");
workbook.SaveAs(ConfigurationManager.AppSettings["reportFolder"] + reportName + ".xlsx");
excel.Workbooks.Close();
excel.Quit();
Console.ForegroundColor = ConsoleColor.DarkGreen;
Console.WriteLine($"Processing file {inputFileName} is completed.");
Console.ForegroundColor = color;
}
catch (System.Runtime.InteropServices.COMException)
{
foreach (Process pr in prs)
{
if (pr.ProcessName == "EXCEL")
{
pr.Kill();
}
}
}
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
//Console.WriteLine("Press \'q\' to quit the console.");
//while (Console.Read() != 'q') ;
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
}
#region clean all excel processes again just in case
catch (Exception e)
{
foreach (Process pr in prs)
{
if (pr.ProcessName == "EXCEL" && pr.ProcessName == "FastCustom")
{
pr.Kill();
}
}
//this will use the "fileLogger" logger from our NLog.config file
Logger logger = LogManager.GetLogger("fileLogger");
//add custom message and pass in the exception
logger.Error(e, "Need fix some problems!");
}
#endregion
}
public string CreatePdfFromExcel(int id = 0)
{
try {
string workbookPath = @"C:\Users\fwang\source\Workspaces\Workspace\RiskNet\Dashboard\DashboardBranch\Presentation\Nop.Web\ExportPdfFiles\Temp";
var directory = new DirectoryInfo(workbookPath);
if (id != 0)
{
foreach (FileInfo myFile in directory.GetFiles())
{
try {
myFile.Delete();
} catch (Exception ex) {
}
}
return("");
}
foreach (FileInfo myFile in directory.GetFiles("*.xlsx").OrderByDescending(f => f.LastWriteTime))
{
using (new Impersonator("fwang", "mobius", "Yibing9!")) {
Microsoft.Office.Interop.Excel.Application excelApplication;
Microsoft.Office.Interop.Excel.Workbook excelWorkbook;
excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.ScreenUpdating = false;
excelApplication.DisplayAlerts = false;
excelWorkbook = excelApplication.Workbooks.Open(myFile.FullName);
Microsoft.Office.Interop.Excel.Worksheet ws = excelWorkbook.Sheets[1];
ws.PageSetup.Zoom = false;
ws.PageSetup.FitToPagesTall = false;
ws.PageSetup.FitToPagesWide = 1;
try {
excelWorkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, myFile.FullName + Guid.NewGuid().ToString() + ".pdf");
} catch (System.Exception ex) {
} finally {
excelWorkbook.Close();
excelApplication.Quit();
excelApplication = null;
excelWorkbook = null;
}
}
}
}catch (Exception ex) {
string s = ex.Message + " " + ex.StackTrace;
return(s);
}
return("test");
MySimpleClass o = new MySimpleClass();
MySimpleClass o2 = new MySimpleClass();
o.StringVal1 = "Test test";
o.StringVal2 = "test2";
try {
this.AutoMapper(o, o2);
}catch (Exception ex) {
StackTrace st = new StackTrace(true);
string stackIndent = "";
for (int i = 0; i < st.FrameCount; i++)
{
// Note that at this level, there are four
// stack frames, one for each method invocation.
StackFrame sf = st.GetFrame(i);
Console.WriteLine();
Console.WriteLine(stackIndent + " Method: {0}",
sf.GetMethod());
Console.WriteLine(stackIndent + " File: {0}",
sf.GetFileName());
Console.WriteLine(stackIndent + " Line Number: {0}",
sf.GetFileLineNumber());
stackIndent += " ";
}
throw ex;
}
return(o2.StringVal1);
}
private void ExcelCreation(DataSet dtDataByfilter)
{
try
{
dtDataByfilter.Tables[0].TableName = "Target";
// dtDataByfilter.Tables[1].TableName = "Incentive Plan";
// dtDataByfilter.Tables[2].TableName = "Report";
string myServerPath = Server.MapPath("~/ExcelTemplate/SalePurchase-Template.xlsx");
//var excel = new Microsoft.Office.Interop.Excel.Application();
//var workbook = excel.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Visible = true;
//excel.Worksheets.Add(dtDataByfilter.Tables[0], "Target1");
Microsoft.Office.Interop.Excel.Workbook wbExiting = excel.Workbooks.Open(myServerPath);
Microsoft.Office.Interop.Excel.Worksheet sh = wbExiting.Sheets[1];
if (dtDataByfilter != null && dtDataByfilter.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < dtDataByfilter.Tables[0].Rows.Count; i++)
{
sh.Cells[i + 2, 1] = dtDataByfilter.Tables[0].Rows[i][0];
sh.Cells[i + 2, 2] = dtDataByfilter.Tables[0].Rows[i][1];
sh.Cells[i + 2, 3] = dtDataByfilter.Tables[0].Rows[i][2];
sh.Cells[i + 2, 4] = dtDataByfilter.Tables[0].Rows[i][3];
sh.Cells[i + 2, 5] = dtDataByfilter.Tables[0].Rows[i][4];
sh.Cells[i + 2, 6] = dtDataByfilter.Tables[0].Rows[i][5];
sh.Cells[i + 2, 7] = dtDataByfilter.Tables[0].Rows[i][6];
sh.Cells[i + 2, 8] = dtDataByfilter.Tables[0].Rows[i][7];
sh.Cells[i + 2, 9] = dtDataByfilter.Tables[0].Rows[i][8];
}
}
string strUniqueFileName = string.Empty;
strUniqueFileName = Session["USERID"].ToString();
string myServerPath1 = Server.MapPath("~/ExcelTemplate/" + strUniqueFileName + ".xlsx");
FileInfo file = new FileInfo(myServerPath1);
if (file.Exists)
{
file.Delete();
}
wbExiting.SaveAs(Server.MapPath("~/ExcelTemplate/" + strUniqueFileName + ".xlsx"), 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);
excel.DisplayAlerts = false;
wbExiting.Close(false, Type.Missing, Type.Missing);
excel.Quit();
FileInfo file1 = new FileInfo(myServerPath1);
if (file1.Exists)
{
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=SalePurchase.xlsx");
Response.AddHeader("Content-Type", "application/Excel");
Response.ContentType = "application/vnd.xls";
Response.AddHeader("Content-Length", file1.Length.ToString());
Response.WriteFile(file1.FullName);
// file.Delete();
Response.End();
}
else
{
Response.Write("This file does not exist.");
}
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
}
protected List <lhh_LoginList_info> getlist()
{
try
{
Local_IP();
InfoList = new List <lhh_LoginList_info>();
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
string[] ob = Regex.Split(ZFCEPath, @"\\", RegexOptions.IgnoreCase);
//string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "辣皇后\\user control.xlsx");
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook analyWK = excelApp.Workbooks.Open(ZFCEPath, Type.Missing, true, Type.Missing,
"htc", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)analyWK.Worksheets["配置"];
Microsoft.Office.Interop.Excel.Range rng;
rng = WS.get_Range(WS.Cells[2, 7], WS.Cells[WS.UsedRange.Rows.Count, 10]);
int rowCount = WS.UsedRange.Rows.Count - 2;
object[,] o = new object[1, 1];
o = (object[, ])rng.Value2;
clsCommHelp.CloseExcel(excelApp, analyWK);
for (int i = 1; i <= rowCount; i++)
{
// bgWorker.ReportProgress(0, "读入数据中 : " + i.ToString() + "/" + rowCount.ToString());
lhh_LoginList_info temp = new lhh_LoginList_info();
#region 基础信息
if (o[i, 1] != null)
{
temp.loginid = o[i, 1].ToString().Trim();
}
if (o[i, 2] != null)
{
temp.pwd = o[i, 2].ToString().Trim();
}
if (o[i, 3] != null)
{
temp.startTime = o[i, 3].ToString().Trim();
}
if (o[i, 4] != null)
{
temp.endTime = Convert.ToDateTime(o[i, 4].ToString());
}
#endregion
InfoList.Add(temp);
}
return(InfoList);
}
catch (Exception ex)
{
MessageBox.Show("表格存在异常,请参照使用说明修改:" + ex.Message);
throw ex;
}
}
private void ReadDataForAccount(string SelectedAccount)
{
try
{
List <int> rownumbers = new List <int>();
//specify the file name where its actually exist
var readOutputfile = Properties.Settings.Default.OutputFolderPath;
string filepath = string.Empty;
int rows_count = 0;
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
try
{
DirectoryInfo dirInfo = new DirectoryInfo(readOutputfile + SelectedAccount + "//");
var files = dirInfo.GetFiles();
int counter = 1;
foreach (FileInfo fileInfo in files)
{
filepath = readOutputfile + SelectedAccount + "\\" + fileInfo.Name;
Microsoft.Office.Interop.Excel.Workbook WB = oExcel.Workbooks.Open(filepath);
try
{
// statement get the workbookname
string ExcelWorkbookname = WB.Name;
// statement get the worksheet count
int worksheetcount = WB.Worksheets.Count;
Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[1];
// statement get the firstworksheetname
Microsoft.Office.Interop.Excel.Range range = wks.UsedRange;
if (counter == 1)
{
rows_count = range.Rows.Count;
}
string firstworksheetname = wks.Name;
if (counter == 1)
{
CreateColumnsForGrid(null);
dataGridView1.Rows.Add(rows_count - 17);
//statement get the first cell value
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
dataGridView1.Columns[i - 1].HeaderText = wks.Cells[1, i].Text;
}
}
if (counter > 1)
{
dataGridView1.Columns.Add(fileInfo.Name.Replace(fileInfo.Extension, ""), fileInfo.Name.Replace(fileInfo.Extension, ""));
}
// storing Each row and column value to excel sheet
if (counter == 1)
{
for (int i = 0; i < rows_count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, j + 1].Text;
decimal result;
if (decimal.TryParse(dataGridView1.Rows[i].Cells[j].Value.ToString(), out result))
{
if (!rownumbers.Exists(a => a == i))
{
rownumbers.Add(i);
}
}
}
}
}
else
{
for (int i = 0; i < rows_count - 1; i++)
{
for (int j = dataGridView1.Columns.Count - 1; j < dataGridView1.Columns.Count; j++)
{
dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, 4].Text;
}
}
}
counter++;
}
catch (Exception e)
{
MessageBox.Show("Issue occured.." + e.Message);
}
finally
{
if (WB != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(WB);
}
}
}
DisplayAverageData(rownumbers);
MessageBox.Show("Data read successfully");
}
catch (Exception ex)
{
// MessageBox.Show("Something went wrong.." + ex.Message);
}
finally
{
//oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
}
}
catch (Exception ex)
{
MessageBox.Show("There was a problem reading the excel sheet, please try again in sometime! " + ex.Message);
}
}
private void ReadDataForAccountAndYear(string SelectedAccount, string selectedYear)
{
try
{
var readOutputfile = Properties.Settings.Default.OutputFolderPath;
DirectoryInfo dirInfo = new DirectoryInfo(readOutputfile + SelectedAccount + "//" + selectedYear + "//");
if (dirInfo.Exists)
{
var directs = dirInfo.GetDirectories();
if (directs.Length > 0)
{
var messageResult = MessageBox.Show("Do you like to load all months data.", "Month Data Alert", MessageBoxButtons.YesNo);
if (messageResult == System.Windows.Forms.DialogResult.Yes)
{
int counter = 1;
foreach (var directory in directs)
{
List <int> rownumbers = new List <int>();
//specify the file name where its actually exist
string filepath = string.Empty;
int rows_count = 0;
try
{
var files = directory.GetFiles();
foreach (FileInfo fileInfo in files)
{
if (!fileInfo.Name.StartsWith("~"))
{
filepath = directory.FullName + "\\" + fileInfo.Name;
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook WB = oExcel.Workbooks.Open(filepath);
// statement get the workbookname
string ExcelWorkbookname = WB.Name;
// statement get the worksheet count
int worksheetcount = WB.Worksheets.Count;
Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[1];
try
{
// statement get the firstworksheetname
Microsoft.Office.Interop.Excel.Range range = wks.UsedRange;
rows_count = range.Rows.Count;
string firstworksheetname = wks.Name;
if (counter == 1)
{
CreateColumnsForGrid(null);
dataGridView1.Rows.Add(rows_count - 17);
//statement get the first cell value
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
dataGridView1.Columns[i - 1].HeaderText = wks.Cells[1, i].Text;
}
}
if (counter > 1)
{
dataGridView1.Columns.Add(fileInfo.Name.Replace(fileInfo.Extension, ""), fileInfo.Name.Replace(fileInfo.Extension, ""));
}
// storing Each row and column value to excel sheet
if (counter == 1)
{
for (int i = 0; i < rows_count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, j + 1].Text;
decimal result;
if (decimal.TryParse(dataGridView1.Rows[i].Cells[j].Value.ToString(), out result))
{
if (!rownumbers.Exists(a => a == i))
{
rownumbers.Add(i);
}
}
}
}
}
else
{
for (int i = 0; i < rows_count - 1; i++)
{
for (int j = dataGridView1.Columns.Count - 1; j < dataGridView1.Columns.Count; j++)
{
dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, 4].Text;
}
}
}
counter++;
}
catch (Exception e)
{
MessageBox.Show("Issue occured.." + e.Message);
}
finally
{
if (wks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wks);
wks = null;
}
if (WB != null)
{
WB.Close(false, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(WB);
WB = null;
}
if (oExcel != null)
{
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
}
DisplayAverageData(rownumbers);
}
catch (Exception ex)
{
// MessageBox.Show("Something went wrong.." + ex.Message);
}
}
MessageBox.Show("Data read successfully");
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("There was a problem reading the excel sheet, please try again in sometime! " + ex.Message);
}
}
private void ExportToExcel(DataTable DataTable, string ExcelFilePath = null)
{
try
{
int ColumnsCount;
if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks.Add();
// single worksheet
Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;
//object[] Header = new object[ColumnsCount];
//// column headings
//for (int i = 0; i < ColumnsCount; i++)
// Header[i] = DataTable.Columns[i].ColumnName;
//Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)
// (Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
//HeaderRange.Value = Header;
//HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
//HeaderRange.Font.Bold = true;
// DataCells
int RowsCount = DataTable.Rows.Count;
object[,] Cells = new object[RowsCount, ColumnsCount];
for (int j = 0; j < RowsCount; j++)
{
for (int i = 0; i < ColumnsCount; i++)
{
Cells[j, i] = DataTable.Rows[j][i];
}
}
Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]),
(Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount, ColumnsCount])).Value = Cells;
//Worksheet.Columns.AutoFit();//Extend column width if necessary
// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
Worksheet.Cells.NumberFormat = "@";//Type of all cells set to text
Worksheet.SaveAs(ExcelFilePath);
Excel.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
Excel.Visible = true;
}
}
catch (Exception ex)
{
WriteLog(ex.ToString());
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\WerkStudent\Mail\Gen_Open_Fin_kel");
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
double cF = 1 / 0.3048;
//Get UI document
UIDocument uidoc = commandData.Application.ActiveUIDocument;
//Get document
Document doc = uidoc.Document;
string Level_1 = (String)xlRange.Cells[4, "A"].Value2;
//Create levels
Level Level1 = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Levels)
.WhereElementIsNotElementType().Cast <Level>().First(x => x.Name == Level_1);
Level Level2 = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Levels)
.WhereElementIsNotElementType().Cast <Level>().First(x => x.Name == "Level 2");
string ty1 = (String)xlRange.Cells[3, "A"].Value2;
var FloorType = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Floors)
.WhereElementIsElementType().Cast <FloorType>().First(x => x.Name == ty1);
//var Floor = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_FloorsCut)
// .WhereElementIsElementType().Cast<Floor>().First(x => x.Name == ty1);
int points = Convert.ToInt32(xlRange.Cells[2, "C"].value2);
string keller = (String)xlRange.Cells[2, "F"].value2;
int geschoss = Convert.ToInt32(xlRange.Cells[3, "F"].value2);
List <XYZ> pointlistkf = new List <XYZ>();
List <XYZ> pointlistgf = new List <XYZ>();
List <XYZ> pointlistufs = new List <XYZ>();
for (int i = 5; i <= points + 4; i++)
{
if (keller == "Ja")
{
XYZ ptskf = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, -3.06 * cF);
pointlistkf.Add(ptskf);
XYZ ptsgf = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, 0 * cF);
pointlistgf.Add(ptsgf);
for (int j = 0; j < geschoss; j++)
{
XYZ ptsufs = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, (xlRange.Cells[i, "D"].value2 + (j * 3.06)) * cF);
pointlistufs.Add(ptsufs);
}
}
else
{
XYZ ptsgf = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, 0 * cF);
pointlistgf.Add(ptsgf);
for (int j = 0; j < geschoss; j++)
{
XYZ ptsufs = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, (xlRange.Cells[i, "D"].value2 + (j * 3.06)) * cF);
pointlistufs.Add(ptsufs);
}
}
}
List <Line> concurveskf = new List <Line>();
List <Line> concurvesgf = new List <Line>();
for (int i = 0; i < pointlistkf.Count; i++)
{
if (i < pointlistkf.Count - 1)
{
Line Alllines = Line.CreateBound(pointlistkf.ElementAt(i), pointlistkf.ElementAt(i + 1));
concurveskf.Add(Alllines);
}
else
{
Line Alllines = Line.CreateBound(pointlistkf.ElementAt(pointlistkf.Count - 1), pointlistkf.ElementAt(0));
concurveskf.Add(Alllines);
}
}
for (int i = 0; i < pointlistgf.Count; i++)
{
if (i < pointlistgf.Count - 1)
{
Line Alllines = Line.CreateBound(pointlistgf.ElementAt(i), pointlistgf.ElementAt(i + 1));
concurvesgf.Add(Alllines);
}
else
{
Line Alllines = Line.CreateBound(pointlistgf.ElementAt(pointlistgf.Count - 1), pointlistgf.ElementAt(0));
concurvesgf.Add(Alllines);
}
}
List <Line> concurvesuf = new List <Line>();
List <Line> concurvesuf1 = new List <Line>();
List <Line> concurvesuf2 = new List <Line>();
switch (geschoss)
{
case 1:
break;
case 2:
for (int i = 0; i < 4; i++)
{
if (i < 3)
{
Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(i + 1));
concurvesuf.Add(Alllines);
}
else
{
Line Alllines = Line.CreateBound(pointlistufs.ElementAt(pointlistufs.Count - 1), pointlistufs.ElementAt(0));
concurvesuf.Add(Alllines);
}
}
break;
case 3:
for (int i = 0; i < 4; i++)
{
if (i < 3)
{
Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(i + 1));
Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 5));
concurvesuf.Add(Alllines);
concurvesuf1.Add(Alllines1);
}
else
{
Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(0));
Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 1));
concurvesuf.Add(Alllines);
concurvesuf1.Add(Alllines1);
}
}
break;
case 4:
for (int i = 0; i < 4; i++)
{
if (i < 3)
{
Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(i + 1));
Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 5));
Line Alllines2 = Line.CreateBound(pointlistufs.ElementAt(i + 8), pointlistufs.ElementAt(i + 9));
concurvesuf.Add(Alllines);
concurvesuf1.Add(Alllines1);
concurvesuf2.Add(Alllines2);
}
else
{
Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(0));
Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 1));
Line Alllines2 = Line.CreateBound(pointlistufs.ElementAt(i + 8), pointlistufs.ElementAt(i + 5));
concurvesuf.Add(Alllines);
concurvesuf1.Add(Alllines1);
concurvesuf2.Add(Alllines2);
}
}
break;
}
List <Curve> licurvekf = new List <Curve>();
List <Curve> licurvegf = new List <Curve>();
for (int i = 0; i < concurveskf.Count; i++)
{
licurvekf.Add(concurveskf.ElementAt(i));
}
for (int i = 0; i < concurvesgf.Count; i++)
{
licurvegf.Add(concurvesgf.ElementAt(i));
}
List <Curve> licurveuf = new List <Curve>();
for (int i = 0; i < concurvesuf.Count; i++)
{
licurveuf.Add(concurvesuf.ElementAt(i));
}
List <Curve> licurveuf1 = new List <Curve>();
for (int i = 0; i < concurvesuf1.Count; i++)
{
licurveuf1.Add(concurvesuf1.ElementAt(i));
}
List <Curve> licurveuf2 = new List <Curve>();
for (int i = 0; i < concurvesuf2.Count; i++)
{
licurveuf2.Add(concurvesuf2.ElementAt(i));
}
// Making a curve in a loop
List <CurveLoop> loops = new List <CurveLoop>();
CurveLoop crvloop0 = CurveLoop.Create(licurvekf);
loops.Add(crvloop0);
CurveLoop crvloop = CurveLoop.Create(licurvegf);
loops.Add(crvloop);
CurveLoop crvloop1 = CurveLoop.Create(licurveuf);
loops.Add(crvloop1);
CurveLoop crvloop2 = CurveLoop.Create(licurveuf1);
loops.Add(crvloop2);
CurveLoop crvloop3 = CurveLoop.Create(licurveuf2);
loops.Add(crvloop3);
List <CurveArray> curArri = new List <CurveArray>();
for (int i = 0; i < loops.Count; i++)
{
CurveArray curArr = new CurveArray();
foreach (Curve c in loops[i])
{
//Put the curves to curve array
curArr.Append(c);
}
curArri.Add(curArr);
}
//
List <XYZ> opointlist = new List <XYZ>();
for (int i = 3; i <= 6; i++)
{
XYZ opoints = new XYZ(xlRange.Cells[i, "H"].value2 * cF, xlRange.Cells[i, "I"].value2 * cF, xlRange.Cells[i, "J"].value2 * cF);
opointlist.Add(opoints);
}
List <Line> opline = new List <Line>();
for (int i = 0; i < opointlist.Count; i++)
{
if (i < opointlist.Count - 1)
{
Line oline = Line.CreateBound(opointlist.ElementAt(i), opointlist.ElementAt(i + 1));
opline.Add(oline);
}
else
{
Line oline = Line.CreateBound(opointlist.ElementAt(3), opointlist.ElementAt(0));
opline.Add(oline);
}
}
List <Curve> ocurve = new List <Curve>();
for (int i = 0; i < opline.Count; i++)
{
ocurve.Add(opline.ElementAt(i));
}
CurveLoop crvvloopopen = CurveLoop.Create(ocurve);
CurveArray curvopen = new CurveArray();
foreach (Curve co in crvvloopopen)
{
//Put the curves to curve array
curvopen.Append(co);
}
//
try
{
Floor FL;
List <Floor> stories = new List <Floor>();
if (keller == "Ja")
{
for (int i = 0; i <= geschoss; i++)
{
using (Transaction trans = new Transaction(doc, "Bungalow Floors"))
{
trans.Start();
FL = doc.Create.NewFloor(curArri[i], FloorType, Level1, false);
stories.Add(FL);
trans.Commit();
}
}
}
else
{
for (int i = 0; i < geschoss; i++)
{
using (Transaction trans = new Transaction(doc, "Bungalow Floors"))
{
trans.Start();
FL = doc.Create.NewFloor(curArri[i], FloorType, Level1, false);
stories.Add(FL);
trans.Commit();
}
}
}
for (int i = 1; i < stories.Count; i++)
{
using (Transaction trans1 = new Transaction(doc, "Bung open"))
{
trans1.Start();
//var Open = doc.Create.NewFloor(curArr1, FloorType, Level2, false);
doc.Create.NewOpening(stories[i], curvopen, true);
trans1.Commit();
}
}
return(Result.Succeeded);
}
catch (Exception e)
{
message = e.Message;
return(Result.Failed);
}
}
private void Export(DataTable dt, string sheetName, string title)
{
//Tạo các đối tượng Excel
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks oBooks;
Microsoft.Office.Interop.Excel.Sheets oSheets;
Microsoft.Office.Interop.Excel.Workbook oBook;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
//Tạo mới một Excel WorkBook
oExcel.Visible = true;
oExcel.DisplayAlerts = false;
oExcel.Application.SheetsInNewWorkbook = 1;
oBooks = oExcel.Workbooks;
oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
oSheets = oBook.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
oSheet.Name = sheetName;
try
{
// Tạo phần đầu nếu muốn
Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "O1");
head.MergeCells = true;
head.Value2 = title;
head.Font.Bold = true;
head.Font.Name = "Tahoma";
head.Font.Size = "18";
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo tiêu đề cột
Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
cl1.Value2 = "Mã KH";
cl1.ColumnWidth = 10.5;
Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
cl2.Value2 = "Họ KH";
cl2.ColumnWidth = 20.0;
Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
cl3.Value2 = "Tên KH";
cl3.ColumnWidth = 20.0;
Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3");
cl4.Value2 = "Giới tính";
cl4.ColumnWidth = 10.0;
Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3");
cl5.Value2 = "Ngày sinh";
cl5.ColumnWidth = 15.0;
Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3");
cl6.Value2 = "Điện thoại";
cl6.ColumnWidth = 15.0;
Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("G3", "G3");
cl7.Value2 = "Địa chỉ";
cl7.ColumnWidth = 25.0;
Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("H3", "H3");
cl8.Value2 = "CMND";
cl8.ColumnWidth = 15.0;
Microsoft.Office.Interop.Excel.Range cl9 = oSheet.get_Range("I3", "I3");
cl9.Value2 = "Ngày mua";
cl9.ColumnWidth = 15;
Microsoft.Office.Interop.Excel.Range cl10 = oSheet.get_Range("J3", "J3");
cl10.Value2 = "Sổ BH";
cl10.ColumnWidth = 10.0;
Microsoft.Office.Interop.Excel.Range cl11 = oSheet.get_Range("K3", "K3");
cl11.Value2 = "Tên xe";
cl11.ColumnWidth = 15.0;
Microsoft.Office.Interop.Excel.Range cl12 = oSheet.get_Range("L3", "L3");
cl12.Value2 = "Biển số";
cl12.ColumnWidth = 15.0;
Microsoft.Office.Interop.Excel.Range cl13 = oSheet.get_Range("M3", "M3");
cl13.Value2 = "Số khung";
cl13.ColumnWidth = 15.0;
Microsoft.Office.Interop.Excel.Range cl14 = oSheet.get_Range("N3", "N3");
cl14.Value2 = "Số máy";
cl14.ColumnWidth = 15.0;
Microsoft.Office.Interop.Excel.Range cl15 = oSheet.get_Range("O3", "O3");
cl15.Value2 = "Loại KH";
cl15.ColumnWidth = 10.0;
Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "O3");
rowHead.Font.Bold = true;
// Kẻ viền
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Thiết lập màu nền
rowHead.Interior.ColorIndex = 15;
rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
// vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
//Chuyển dữ liệu từ DataTable vào mảng đối tượng
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
//Thiết lập vùng điền dữ liệu
int rowStart = 4;
int columnStart = 1;
int rowEnd = rowStart + dt.Rows.Count - 1;
int columnEnd = dt.Columns.Count;
// Ô bắt đầu điền dữ liệu
Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
// Ô kết thúc điền dữ liệu
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
// Lấy về vùng điền dữ liệu
Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);
//Điền dữ liệu vào vùng đã thiết lập
range.Value2 = arr;
// Kẻ viền
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Căn giữa cột STT
Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
private void btnExportXL_Click(object sender, RoutedEventArgs e)
{
try
{
dt = ((DataView)dgVariationReport.ItemsSource).ToTable();
if (dt.Rows.Count > 0)
{
Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
dlg.InitialDirectory = Environment.CurrentDirectory;
dlg.Title = "Variation Report";
dlg.DefaultExt = ".xlsx";
dlg.Filter = "XL files|*.xls;*.xlsx|All files|*.*";
dlg.FileName = "VariationReport";
if (dlg.ShowDialog() == true)
{
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 = "Variation Report";
worksheet.Cells[3, 4] = "VARIATION REPORT ";
worksheet.Cells[3, 4].Font.Bold = true;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
if (i == 3)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Member", dtpFromDate.SelectedDate);
}
else if (i == 4)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Amount", dtpFromDate.SelectedDate);
}
else if (i == 5)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Member", dtpFromDate.SelectedDate);
}
else if (i == 6)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Amount", dtpFromDate.SelectedDate);
}
else if (i == 7)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Member", dtpFromDate.SelectedDate);
}
else if (i == 8)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Amount", dtpFromDate.SelectedDate);
}
else if (i == 9)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Member", dtpToDate.SelectedDate);
}
else if (i == 10)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Amount", dtpToDate.SelectedDate);
}
else if (i == 11)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Member", dtpToDate.SelectedDate);
}
else if (i == 12)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Amount", dtpToDate.SelectedDate);
}
else if (i == 13)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Member", dtpToDate.SelectedDate);
}
else if (i == 14)
{
worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Amount", dtpToDate.SelectedDate);
}
else if (i != 1)
{
worksheet.Cells[5, i + 2] = dt.Columns[i - 1].ColumnName;
}
worksheet.Cells[5, i + 2].Font.Bold = true;
}
for (int i = 0; i < dt.Rows.Count - 1; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j != 0)
{
worksheet.Cells[i + 6, j + 3] = dt.Rows[i][j].ToString();
}
}
}
workbook.SaveAs(dlg.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();
MessageBox.Show("Exported Sucessfully !", "Sucess");
}
}
else
{
MessageBox.Show("No Data Found !", "Empty");
}
}
catch (Exception ex)
{
ExceptionLogging.SendErrorToText(ex);
}
}
public void Export(DataTable dt, string sheetName, string title)
{
//Tạo các đối tượng Excel
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks oBooks;
Microsoft.Office.Interop.Excel.Sheets oSheets;
Microsoft.Office.Interop.Excel.Workbook oBook;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
//Tạo mới một Excel WorkBook
oExcel.Visible = true;
oExcel.DisplayAlerts = false;
oExcel.Application.SheetsInNewWorkbook = 1;
oBooks = oExcel.Workbooks;
oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
oSheets = oBook.Worksheets;
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
oSheet.Name = sheetName;
// Tạo phần đầu
Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "H1");
head.MergeCells = true;
head.Value2 = title;
head.Font.Bold = true;
head.Font.Name = "Tahoma";
head.Font.Size = "18";
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo tiêu đề cột
Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
cl1.Value2 = "Mã nhân viên";
cl1.ColumnWidth = 10.5;
Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
cl2.Value2 = "Tên Nhân viên";
cl2.ColumnWidth = 25.0;
Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
cl3.Value2 = "Giới tính";
cl3.ColumnWidth = 10.0;
Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3");
cl4.Value2 = "Ngày sinh";
cl4.ColumnWidth = 13.0;
Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3");
cl5.Value2 = "Mail";
cl5.ColumnWidth = 25.0;
Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3");
cl6.Value2 = "Số điện thoại";
cl6.ColumnWidth = 20.0;
Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("G3", "G3");
cl7.Value2 = "Địa chỉ";
cl7.ColumnWidth = 30.0;
Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("H3", "H3");
cl8.Value2 = "Vị trí";
cl8.ColumnWidth = 13.0;
Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "H3");
rowHead.Font.Bold = true;
// Kẻ viền
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Thiết lập màu nền
rowHead.Interior.ColorIndex = 15;
rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
// vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
//Chuyển dữ liệu từ DataTable vào mảng đối tượng
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
string tmp = dr[c].ToString();
arr[r, c] = tmp;
}
}
//Thiết lập vùng điền dữ liệu
int rowStart = 4;
int columnStart = 1;
int rowEnd = rowStart + dt.Rows.Count - 1;
int columnEnd = dt.Columns.Count;
// Ô bắt đầu điền dữ liệu
Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
// Ô kết thúc điền dữ liệu
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
// Lấy về vùng điền dữ liệu
Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);
//Điền dữ liệu vào vùng đã thiết lập
range.Value2 = arr;
// Kẻ viền
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Căn giữa cột STT
Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//
releaseObject(oSheet);
releaseObject(oBook);
releaseObject(oSheets);
releaseObject(oBooks);
releaseObject(oExcel);
}
public ActionResult RunXlsCampania(string cod_unidad_negocio, string fec_informe, string campania, int batch_desde, int batch_hasta)
{
if (Session["cod_unidad_negocio"] != null)
{
cod_unidad_negocio = Session["cod_unidad_negocio"].ToString();
}
var mensajeError = string.Empty;
var tablero = string.Empty;
var codUnidadNegocio = cod_unidad_negocio;
var fecInforme = fec_informe;
var batchDesde = batch_desde;
var batchHasta = batch_hasta;
var resultado = string.Empty;
var mensaje = string.Empty;
var archivoDestino = string.Empty;
int pid = 0;
//Identificar el archivo a descargar, incluyendo la ruta de acceso.
var excelFuente = string.Empty;
var excelDestino = string.Empty;
var fileDestino = string.Empty;
excelFuente = Server.MapPath("~/xlso/KPI_PI_Campañas.xlsm");
DateTime?dtfecInforme = null;
if (!(fec_informe == "" || fec_informe == string.Empty))
{
dtfecInforme = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", CultureInfo.InvariantCulture);
//Cambiamos el nombre del archivo Destino
fileDestino = "Campaña" + "_" + campania + "_" + codUnidadNegocio + "_" + dtfecInforme.Value.ToString("yyMMdd") + DateTime.Now.ToString("hhmmsss") + ".xlsx";
}
else
{
fileDestino = "Campaña" + "_" + campania + "_" + codUnidadNegocio + "_" + batchDesde + "_" + batch_hasta + "_" + DateTime.Now.ToString("hhmmsss") + ".xlsx";
}
excelDestino = Server.MapPath("~/xlsd/" + fileDestino);
try
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Application.Visible = false;
excelApp.DisplayAlerts = false;
var excelWorkbook = excelApp.Workbooks.Open(Filename: excelFuente, ReadOnly: true);
GetWindowThreadProcessId(excelApp.Hwnd, out pid);
excelWorkbook.Sheets["Registro"].Range("A2").Value = codUnidadNegocio;
excelWorkbook.Sheets["Registro"].Range("B2").Value = campania;
var fecExcel = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", null).ToString("MM/dd/yyyy");
excelWorkbook.Sheets["Registro"].Range("C2").Value = fecExcel;
excelWorkbook.Sheets["Registro"].Range("D2").Value = excelDestino;
excelWorkbook.Sheets["Registro"].Range("E2").Value = batchDesde;
excelWorkbook.Sheets["Registro"].Range("F2").Value = batchHasta;
try
{
mensajeError = excelApp.Run("BtnRegistro_Carga");
}
catch (Exception)
{
mensajeError = "ERROR: En la ejecución de Excel";
}
if (mensajeError == null)
{
mensajeError = "";
}
mensajeError.Replace("'", "´");
mensajeError.Replace(((char)34).ToString(), "´");
mensajeError.Replace(((char)13).ToString(), "\r");
mensajeError.Replace(((char)10).ToString(), "\r");
//Close Excel
excelWorkbook.Close(SaveChanges: false);
excelWorkbook = null;
excelApp.Quit();
archivoDestino = excelDestino.Replace(@"\", "/");
try
{
//procesoExcel.Kill();
Process.GetProcessById(pid).Kill();
}
catch (Exception)
{
}
}
catch (Exception ex)
{
System.Threading.Thread.Sleep(1000); //Espera un segundo para intentarlo de nuevo
mensajeError += " Servicio de Excel en estos momentos se encuentra sin recursos.\r Por favor intentelo mas tarde.";
try
{
//procesoExcel.Kill();
Process.GetProcessById(pid).Kill();
}
catch (Exception)
{
}
}
if (mensajeError == "")
{
return(Json(
new Response
{
Status = HttpStatusCode.OK,
Message = archivoDestino
},
JsonRequestBehavior.AllowGet));
}
return(Json(
new Response
{
Status = HttpStatusCode.BadRequest,
Message = mensajeError
},
JsonRequestBehavior.AllowGet));
}
public int print(bool b)
{
// 打开一个Excel进程
Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
// 利用这个进程打开一个Excel文件
//Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\CSBag\SOP-MFG-109-R01A 产品内包装记录.xlsx");
Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\8 SOP-MFG-412-R01A 底封机运行记录");
// 选择一个Sheet，注意Sheet的序号是从1开始的
Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[wb.Worksheets.Count];
// 修改Sheet中某行某列的值
fill_excel(my, wb);
//"生产指令-步骤序号- 表序号 /&P"
my.PageSetup.RightFooter = Instruction + "-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count; // &P 是页码
if (b)
{
//true->预览
// 设置该进程是否可见
oXL.Visible = true;
// 让这个Sheet为被选中状态
my.Select(); // oXL.Visible=true 加上这一行 就相当于预览功能
return(0);
}
else
{
int pageCount = 0;
bool isPrint = true;
//false->打印
try
{
// 设置该进程是否可见
//oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
// 直接用默认打印机打印该Sheet
my.PrintOut();
}
catch
{ isPrint = false; }
finally
{
if (isPrint)
{
//写日志
string log = "=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + "：" + mySystem.Parameter.userName + " 打印文档\n";
dt记录.Rows[0]["日志"] = dt记录.Rows[0]["日志"].ToString() + log;
bs记录.EndEdit();
da记录.Update((DataTable)bs记录.DataSource);
}
// 关闭文件，false表示不保存
pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
wb.Close(false);
// 关闭Excel进程
oXL.Quit();
// 释放COM资源
Marshal.ReleaseComObject(wb);
Marshal.ReleaseComObject(oXL);
wb = null;
oXL = null;
}
return(pageCount);
}
}
public static void ExportXLSX(DataGridView dgv, int[] x, int[] y, string[] vanban)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
//Khởi tạo WorkBook
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
//Khởi tạo WorkSheet
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
app.Visible = true;
//Đổ dữ liệu vào Sheet
for (int i = 0; i < vanban.Length; i++)
{
worksheet.Cells[x[i], y[i]] = vanban[i];
}
int dongbatdau = 7;
for (int i = 0; i < dgv.Columns.Count; i++)
{
worksheet.Cells[dongbatdau, i + 1] = dgv.Columns[i].HeaderText;
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
worksheet.Cells[i + 1 + dongbatdau, j + 1] = dgv.Rows[i].Cells[j].Value.ToString();
}
}
worksheet.Range["A" + dongbatdau, "G" + dongbatdau].Font.Bold = true; //đậm tiêu đề cột
worksheet.Range["A1", "J100"].Font.Name = "Times New Roman";
worksheet.Range["A1", "G2"].Font.Bold = true;
worksheet.Range["A2", "G2"].Font.Italic = true;
worksheet.Range["A2", "A2"].Font.Bold = false;
worksheet.Range["C4"].Font.Bold = true;
worksheet.Range["C4"].Font.Size = 18;
//kẻ viền
worksheet.Range["A" + (dongbatdau), Convert.ToChar(dgv.Columns.Count + 65 - 1).ToString() + (dongbatdau + dgv.Rows.Count)].Borders.LineStyle = 1;
int ii = 20;
worksheet.Range["A1"].ColumnWidth = ii;
worksheet.Range["B1"].ColumnWidth = ii;
worksheet.Range["C1"].ColumnWidth = ii;
worksheet.Range["D1"].ColumnWidth = ii;
worksheet.Range["E1"].ColumnWidth = ii;
worksheet.Range["F1"].ColumnWidth = ii;
worksheet.Range["G1"].ColumnWidth = ii;
//tính ttoongr
worksheet.Cells[dongbatdau + dgv.Rows.Count + 1, dgv.Columns.Count] =
string.Format("=SUM({0}:{1})",
Convert.ToChar(dgv.Columns.Count + 64).ToString() + (dongbatdau + 1),
Convert.ToChar(dgv.Columns.Count + 64).ToString() + (dongbatdau + dgv.Rows.Count)
);
worksheet.Cells[dongbatdau + dgv.Rows.Count + 1, dgv.Columns.Count - 1] = "Tổng";
}
private void buttonStart_Click(object sender, EventArgs e)
{
if (textBoxPath.Text != "")
{
this.label1.Visible = true;
this.pictureBox1.Visible = true;
this.label1.Text = "正在整理中......";
this.pictureBox1.Image = Resources._123;
List <String> list = Director(textBoxPath.Text);
foreach (var path in list)
{
CrawlData(path);
}
}
try
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = true;
//新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错
Microsoft.Office.Interop.Excel.Workbook xBook = excel.Workbooks.Add(Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];
xSheet.Cells[1, 1] = "ip地址";
xSheet.Cells[1, 2] = "漏洞名称";
xSheet.Cells[1, 3] = "详细描述";
xSheet.Cells[1, 4] = "加固建议";
xSheet.Cells[1, 5] = "风险端口";
xSheet.Cells[1, 6] = "危险程度";
List <string> loopholeInfoDicKey = new List <string>(loopholeInfoDic.Keys);
for (int i = 0; i < loopholeInfoDicKey.Count; i++)
{
for (int j = 0; j < 6; j++)
{
if (j == 2)
{
if (loopholeInfoDic[loopholeInfoDicKey[i]].Count > 4)
{
xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][4];
}
}
else if (j == 3)
{
if (loopholeInfoDic[loopholeInfoDicKey[i]].Count > 4)
{
xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][5];
}
}
else if (j == 4)
{
xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][2];
}
else if (j == 5)
{
xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][3];
}
else
{
xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][j];
}
}
}
excel.DisplayAlerts = false;
string saveName = GetSystemSecond().ToString();
string savePath = Application.StartupPath + "\\" + saveName + ".xlsx";
xBook.SaveAs(savePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xSheet = null;
xBook = null;
excel.Quit();
excel = null;
GC.Collect();//如果不使用这条语句会导致excel进程无法正常退出，使用后正常退出
}
catch (Exception ex)
{
MessageBox.Show("保存excel出错: " + ex.Message);
Close();
}
this.label1.Text = "整理完成！";
this.pictureBox1.Image = Resources._234;
}
private void button1_Click(object sender, EventArgs e)
{
List <int> id_select = new List <int>();
SqlConnection connecting = new SqlConnection(Program.connection);
connecting.Open();
string contact = "SELECT ID FROM [Documents] WHERE TypeDoc='" + CbTypeDoc.Text + "' AND DateIssued>='" + dateTimeOt.Value + "' AND DateIssued<='" + dateTimeDo.Value + "'";
SqlCommand cmd = new SqlCommand(contact, connecting);
SqlDataReader dataReader = cmd.ExecuteReader();
try
{
if (dataReader.HasRows) // если есть данные
{
while (dataReader.Read())
{
id_select.Add(Convert.ToInt32(dataReader["ID"]));
}
}
nomera = new int[id_select.Count];
int k = 0;
foreach (int i in id_select)
{
nomera[k] = i;
k++;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
connecting.Close();
try
{
//Объявляем приложение
Microsoft.Office.Interop.Excel.Application ex = new Microsoft.Office.Interop.Excel.Application();
//Отобразить Excel
//Количество листов в рабочей книге
ex.SheetsInNewWorkbook = 1;
//Добавить рабочую книгу
Microsoft.Office.Interop.Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing);
//Получаем первый лист документа (счет начинается с 1)
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)ex.Worksheets.get_Item(1);
string rootFolder = @"D:\"; //Путь
string fileName = $"ExportUsers{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"; // название файла чтобы не повторялось
FileInfo file = new FileInfo(rootFolder + fileName); //создаем файл
// Microsoft.Office.Interop.Excel.Application package = new Microsoft.Office.Interop.Excel.Application();
// package.Visible = true;
// string rootFolder = @"D:\"; //Путь
// string fileName = "shab";
//
// FileInfo file = new FileInfo(rootFolder + fileName);
var excelcells = worksheet.get_Range("A1", "Q1");
excelcells.Merge(Type.Missing);
excelcells.Value = "Выписка о выданных документах типа'" + CbTypeDoc.Text + "'";
excelcells.get_Range("A1", "Q1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
var excelcells1 = worksheet.get_Range("A2", "Q2");
excelcells1.Merge(Type.Missing);
excelcells1.Value = "Период: от '" + dateTimeOt.Value.ToShortDateString() + "' до '" + dateTimeDo.Value.ToShortDateString() + "'";
excelcells1.get_Range("A2", "Q2").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
// ExcelWorksheet worksheet = package.Worksheets.Add("Дипломы"); //Необязательно
worksheet.Cells[3, 1].Value = "Вид документа";
worksheet.Cells[3, 2].Value = "Статус документа";
worksheet.Cells[3, 3].Value = "Подтверждение утраты";
worksheet.Cells[3, 4].Value = "Подтверждение обмена";
worksheet.Cells[3, 5].Value = "Подтверждение уничтожения";
worksheet.Cells[3, 6].Value = "Серия документа";
worksheet.Cells[3, 7].Value = "Номер документа";
worksheet.Cells[3, 8].Value = "Дата выдачи документа";
worksheet.Cells[3, 9].Value = "Регистрационный номер";
worksheet.Cells[3, 10].Value = "Дополнительная профессиональная программа";
worksheet.Cells[3, 11].Value = "Наименование дополнительной профессиональной программы";
worksheet.Cells[3, 12].Value = "Дата начала обучения";
worksheet.Cells[3, 13].Value = "Дата окончания обучения";
worksheet.Cells[3, 14].Value = "Фамилия слушателя";
worksheet.Cells[3, 15].Value = "Имя слушателя";
worksheet.Cells[3, 16].Value = "Отчество слушателя";
worksheet.Cells[3, 17].Value = "Дата рождения";
SqlConnection connect = new SqlConnection(Program.connection);
connect.Open();
//выбираем статус документа
// SqlCommand statusdoc = new SqlCommand("SELECT StatusDoc FROM [Documents] WHERE ID =" + nomera[1], connect);
// statusdoc.ExecuteScalar();
// MessageBox.Show(Convert.ToString(statusdoc.ExecuteScalar()));
for (int u = 0; u < nomera.Length; u++)
{
//выбираем статус документа
SqlCommand statusdoc = new SqlCommand("SELECT StatusDoc FROM [Documents] WHERE ID =" + nomera[u], connect);
statusdoc.ExecuteScalar();
//выбираем утрату документа
SqlCommand utrata = new SqlCommand("SELECT Utrata FROM [Documents] WHERE ID =" + nomera[u], connect);
utrata.ExecuteScalar();
//выбираем обмен документа
SqlCommand obmen = new SqlCommand("SELECT Obmen FROM [Documents] WHERE ID =" + nomera[u], connect);
obmen.ExecuteScalar();
//выбираем уничтожение документа
SqlCommand unichtog = new SqlCommand("SELECT Unichtog FROM [Documents] WHERE ID =" + nomera[u], connect);
unichtog.ExecuteScalar();
//выбираем серию документа
SqlCommand seriesdoc = new SqlCommand("SELECT SeriesDoc FROM [Documents] WHERE ID =" + nomera[u], connect);
seriesdoc.ExecuteScalar();
//выбираем номер документа
SqlCommand numberdoc = new SqlCommand("SELECT NumberDoc FROM [Documents] WHERE ID =" + nomera[u], connect);
numberdoc.ExecuteScalar();
//выбираем регистрационный номер документа
SqlCommand regnumber = new SqlCommand("SELECT RegNumber FROM [Documents] WHERE ID =" + nomera[u], connect);
regnumber.ExecuteScalar();
//выбираем дату выдачи документа
SqlCommand dateissued = new SqlCommand("SELECT DateIssued FROM [Documents] WHERE ID =" + nomera[u], connect);
dateissued.ExecuteScalar();
//Выбираем группу к которой привязан курс
SqlCommand gr = new SqlCommand("SELECT [Group] FROM [Documents] WHERE ID=" + nomera[u], connect);
gr.ExecuteScalar();
//выбираем ID курса по названию группы
SqlCommand course = new SqlCommand("SELECT IDCourse FROM [Group] WHERE NameGroup ='" + gr.ExecuteScalar() + "'", connect);
course.ExecuteScalar();
//выбираем тип программы( повышение квалификации..)
SqlCommand nametype = new SqlCommand("SELECT NameType FROM [TypeProg] WHERE ID=(SELECT Type FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "')", connect);
nametype.ExecuteScalar();
//выбираем количество часов в курсе
SqlCommand kolchas = new SqlCommand("SELECT KolChas FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "'", connect);
kolchas.ExecuteScalar();
//выбираем наименование программы (медицинская оптика...)
SqlCommand nameprog = new SqlCommand("SELECT NameProg FROM [Program] WHERE ID=(SELECT Program FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "')", connect);
nameprog.ExecuteScalar();
//выбираем ID курса
SqlCommand idcourse = new SqlCommand("SELECT Course FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "'", connect);
idcourse.ExecuteScalar();
//выбираем наименование курса
SqlCommand namecourse = new SqlCommand("SELECT NameCourse FROM [Course] WHERE IDCourse='" + idcourse.ExecuteScalar() + "'", connect);
namecourse.ExecuteScalar();
//выбираем дату начала курса
SqlCommand datestart = new SqlCommand("SELECT DateStart FROM [NewCourse] WHERE ID=" + course.ExecuteScalar(), connect);
datestart.ExecuteScalar();
//выбираем дату окончания курса
SqlCommand dateend = new SqlCommand("SELECT DateEnd FROM [NewCourse] WHERE ID=" + course.ExecuteScalar(), connect);
dateend.ExecuteScalar();
//выбираем id студента
SqlCommand student = new SqlCommand("SELECT IdStudent FROM [Documents] WHERE ID=" + nomera[u], connect);
student.ExecuteScalar();
//выбираем фамилию студента
SqlCommand surname = new SqlCommand("SELECT Surname FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect);
surname.ExecuteScalar();
//выбираем имя студента
SqlCommand name = new SqlCommand("SELECT Name FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect);
name.ExecuteScalar();
//выбираем отчество студента
SqlCommand patronimyc = new SqlCommand("SELECT Patronymic FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect);
patronimyc.ExecuteScalar();
//выбираем дату рождения студента
SqlCommand dateofbirth = new SqlCommand("SELECT DateOfBirth FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect);
dateofbirth.ExecuteScalar();
worksheet.Cells[u + 4, 1].Value = CbTypeDoc.Text;
worksheet.Cells[u + 4, 2].Value = statusdoc.ExecuteScalar();
worksheet.Cells[u + 4, 3].Value = utrata.ExecuteScalar();
worksheet.Cells[u + 4, 4].Value = obmen.ExecuteScalar();
worksheet.Cells[u + 4, 5].Value = unichtog.ExecuteScalar();
worksheet.Cells[u + 4, 6].Value = seriesdoc.ExecuteScalar();
worksheet.Cells[u + 4, 7].Value = numberdoc.ExecuteScalar();
worksheet.Cells[u + 4, 8].Value = dateissued.ExecuteScalar();
worksheet.Cells[u + 4, 9].Value = regnumber.ExecuteScalar();
worksheet.Cells[u + 4, 10].Value = nametype.ExecuteScalar();
worksheet.Cells[u + 4, 11].Value = nameprog.ExecuteScalar();
worksheet.Cells[u + 4, 12].Value = datestart.ExecuteScalar();
worksheet.Cells[u + 4, 13].Value = dateend.ExecuteScalar();
worksheet.Cells[u + 4, 14].Value = surname.ExecuteScalar();
worksheet.Cells[u + 4, 15].Value = name.ExecuteScalar();
worksheet.Cells[u + 4, 16].Value = patronimyc.ExecuteScalar();
worksheet.Cells[u + 4, 17].Value = dateofbirth.ExecuteScalar();
}
var oRng = worksheet.get_Range("A1", "Q1");
oRng.EntireColumn.AutoFit();
// закрываем соединение с БД
//ex.Visible = true;
// worksheet.SaveAs("otchet");
SaveFileDialog sfd = new SaveFileDialog();
//выбираем путь для сохранения
sfd.InitialDirectory = @"D:\SDO\otchet\";
string path = "";
if (sfd.ShowDialog() == DialogResult.OK)
{
path = sfd.FileName;
worksheet.SaveAs(path);
MessageBox.Show("Файл сохранен");
}
connect.Close();
MessageBox.Show("Файл сформирован");
}
catch (Exception ex)
{
MessageBox.Show("ошибка" + ex);
}
}
private void BtnExport_Click(object sender, EventArgs e)
{
try {
if (dgvMonthlyReports.Rows.Count != 0)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel Files (*.xls)|*.xls";
saveFileDialog1.FileName = "Doctor Wise Summary(" + DateTime.Now.ToString("dd-MM-yy h.mm.ss tt") + ").xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
PathName = saveFileDialog1.FileName;
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Application.Workbooks.Add(Type.Missing);
ExcelApp.Columns.ColumnWidth = 20;
int count = dgvMonthlyReports.Columns.Count;
ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, count]].Merge();
ExcelApp.Cells[1, 1] = "DOCTOR WISE SUMMARY";
ExcelApp.Cells[1, 1].HorizontalAlignment = HorizontalAlignment.Center;
ExcelApp.Cells[1, 1].Font.Size = 12;
ExcelApp.Cells[1, 1].Interior.Color = Color.FromArgb(153, 204, 255);
ExcelApp.Columns.ColumnWidth = 20;
ExcelApp.Cells[2, 1] = "From Date";
ExcelApp.Cells[2, 1].Font.Size = 10;
ExcelApp.Cells[2, 2] = dptMonthly_From.Value.ToString("dd-MM-yyyy");
ExcelApp.Cells[2, 2].Font.Size = 10;
ExcelApp.Cells[3, 1] = "To Date";
ExcelApp.Cells[3, 1].Font.Size = 10;
ExcelApp.Cells[3, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
ExcelApp.Cells[3, 2] = dptMonthly_To.Value.ToString("dd-MM-yyyy");
ExcelApp.Cells[3, 2].Font.Size = 10;
ExcelApp.Cells[4, 1] = "Running Date";
ExcelApp.Cells[4, 1].Font.Size = 10;
ExcelApp.Cells[4, 2] = DateTime.Now.ToString("dd-MM-yyyy");
ExcelApp.Cells[4, 2].Font.Size = 10;
ExcelApp.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
for (int i = 1; i < dgvMonthlyReports.Columns.Count + 1; i++)
{
ExcelApp.Cells[5, i] = dgvMonthlyReports.Columns[i - 1].HeaderText;
ExcelApp.Cells[5, i].ColumnWidth = 25;
ExcelApp.Cells[5, i].EntireRow.Font.Bold = true;
ExcelApp.Cells[5, i].Interior.Color = Color.FromArgb(0, 102, 204);
ExcelApp.Cells[5, i].Font.Size = 10;
ExcelApp.Cells[5, i].Font.Name = "Segoe UI";
ExcelApp.Cells[5, i].Font.Color = Color.FromArgb(255, 255, 255);
ExcelApp.Cells[5, i].Interior.Color = Color.FromArgb(0, 102, 204);
}
for (int i = 0; i <= dgvMonthlyReports.Rows.Count; i++)
{
try
{
for (int j = 0; j < dgvMonthlyReports.Columns.Count; j++)
{
ExcelApp.Cells[i + 6, j + 1] = dgvMonthlyReports.Rows[i].Cells[j].Value.ToString();
ExcelApp.Cells[i + 6, j + 1].BorderAround(true);
ExcelApp.Cells[i + 6, j + 1].Borders.Color = Color.FromArgb(0, 102, 204);
ExcelApp.Cells[i + 6, j + 1].Font.Size = 8;
}
}
catch { }
}
ExcelApp.ActiveWorkbook.SaveAs(PathName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
checkStr = "1";
MessageBox.Show("Successfully Exported to Excel", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("No records found,please change the date and try again!..", "Failed ", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{ MessageBox.Show(ex.Message, "Error!...", MessageBoxButtons.OK, MessageBoxIcon.Error); }
}
/// <summary>
/// 导出Excel 的方法
/// </summary>
private void tslExport_Excel(string fileName, DataGridView myDGV)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
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
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", "Z" + (myDGV.SelectedRows.Count + 10)); //把Execl设置问文本格式
range.NumberFormatLocal = "@";
//写入标题
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
}
//写入数值
int s = 0;
for (int r = 0; r < myDGV.Rows.Count; r++)
{
if (Convert.ToBoolean(myDGV.Rows[r].Cells[0].Value))
{
for (int i = 1; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[s + 2, i] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
s++;
}
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]);
rang.NumberFormat = "000000000000";
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch
{
//fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n");
}
}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
}
private void ExportToExcel(DataTable dt)
{
Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application();
try
{
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet();
osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet1"];
int columnsCount;
if (dt == null || (columnsCount = dt.Columns.Count) == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
object[] Header = new object[columnsCount];
for (int i = 0; i < 7; i++)
{
Header[i] = dt.Columns[i].ColumnName;
}
Header[6] = string.Empty;
Header[7] = "Total Work Time";
Header[8] = string.Empty;
int hours = 6;
for (var i = 6; i <= columnsCount - 1; i++)
{
Header[i] = hours + "h";
hours++;
}
Microsoft.Office.Interop.Excel.Range HeaderRange = osheet.get_Range((Microsoft.Office.Interop.Excel.Range)
(osheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)
(osheet.Cells[1, columnsCount - 2]));
HeaderRange.Value = Header;
HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
HeaderRange.Font.Bold = true;
int rowsCount = dt.Rows.Count;
object[,] Cells = new object[rowsCount, columnsCount];
for (int j = 0; j < rowsCount; j++)
{
for (int i = 0; i < columnsCount - 2; i++)
{
Cells[j, i] = dt.Rows[j][i];
}
}
osheet.get_Range((Microsoft.Office.Interop.Excel.Range)(osheet.Cells[2, 1]),
(Microsoft.Office.Interop.Excel.Range)
(osheet.Cells[rowsCount + 1, columnsCount])).Value = Cells;
oexcel.Columns.AutoFit();
string filePath = string.Empty;
var save_file_dialog = new SaveFileDialog();
save_file_dialog.FileName = "Efficiency " + DateTime.Now.Date.ToString("dd'-'MM'-'yyyy");
save_file_dialog.DefaultExt = ".xlsx";
save_file_dialog.Filter = "Excel File|*.xlsx|All Files|*.*";
if (save_file_dialog.ShowDialog() == DialogResult.OK)
{
filePath = save_file_dialog.FileName;
oexcel.ActiveWorkbook.SaveCopyAs(filePath);
oexcel.ActiveWorkbook.Saved = true;
oexcel.Quit();
}
save_file_dialog.Dispose();
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
private void ExportDepartmentToExcel()
{
int intRowCounter;
int intRowNumberOfRecords;
int intColumnCounter;
int intColumnNumberOfRecords;
// 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 = "OpenOrders";
int cellRowIndex = 1;
int cellColumnIndex = 1;
intRowNumberOfRecords = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Rows.Count;
intColumnNumberOfRecords = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Columns.Count;
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Columns[intColumnCounter].ColumnName;
cellColumnIndex++;
}
cellRowIndex++;
cellColumnIndex = 1;
//Loop through each row and read value from each column.
for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
{
for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Rows[intRowCounter][intColumnCounter].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 = 1;
saveDialog.ShowDialog();
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
catch (System.Exception ex)
{
TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Blue Jay ERP // Design Employee Productivity // Export Department to Excel " + ex.Message);
MessageBox.Show(ex.ToString());
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void Export2Excel()
{
DateTime tgl;
var excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook excelbk = excelApp.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)excelbk.Worksheets["Sheet1"];
int worksheetcol = WORKSHEETSTARTCOL;
int jmlCol = dgvGrid.Columns.Count;
for (int colCount = 0; colCount < jmlCol; colCount++)
{
Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet1.Cells[WORKSHEETSTARTROW, worksheetcol];
if (colCount > 1 && colCount < (jmlCol - JML_COL_TETAP))
{
try
{
tgl = DateTime.Parse(dgvGrid.Columns[colCount].Name);
xlRange.Value2 = tgl.ToString("dd");
//xlRange.Value2 = tgl.ToString("dd-MMM-yyyy") + "(" + tgl.DayOfWeek.ToString().ToUpper().Substring(0, 3) + ")";
}
catch
{
}
}
else
{
xlRange.Value2 = dgvGrid.Columns[colCount].HeaderText;
}
worksheetcol += 1;
}
int worksheetRow = WORKSHEETSTARTROW + 1;
for (int rowCount = 0; rowCount < dgvGrid.Rows.Count; rowCount++)
{
if (dgvGrid.Rows[rowCount].IsNewRow)
{
continue;
}
worksheetcol = WORKSHEETSTARTCOL;
for (int colCount = 0; colCount < jmlCol; colCount++)
{
Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet1.Cells[worksheetRow, worksheetcol];
xlRange.Value2 = dgvGrid.Rows[rowCount].Cells[colCount].Value.ToString();
//xlRange.Font.Background = dataGridView1.Rows[rowCount].Cells[colCount].Style.BackColor;
//xlRange.Font.Color = dgvGrid.Rows[rowCount].Cells[colCount].Style.ForeColor.ToArgb();
if (colCount > 1 && colCount < (jmlCol - JML_COL_TETAP))
{
if (chkUseColor.Checked)
{
xlRange.Value2 = "";
xlRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(dgvGrid.Rows[rowCount].Cells[colCount].Style.BackColor);
}
else
{
switch (dgvGrid.Rows[rowCount].Cells[colCount].Value.ToString())
{
case "0":
xlRange.Value2 = STAT_ALPA;
break;
case "1":
xlRange.Value2 = STAT_KERJA;
break;
case "2":
xlRange.Value2 = STAT_TIDAKABSEN;
break;
case "3":
xlRange.Value2 = STAT_TELAT;
break;
case "4":
xlRange.Value2 = STAT_PULANGCEPAT;
break;
case "5":
xlRange.Value2 = STAT_OFF;
break;
case "6":
xlRange.Value2 = STAT_IJIN;
break;
case "7":
xlRange.Value2 = STAT_OFFMASUK;
break;
case "8":
xlRange.Value2 = STAT_IJIN;
break;
}
}
}
//if (dgvGrid.Rows[rowCount].Cells[colCount].Style.Font != null)
//{
// xlRange.Font.Bold = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.Bold;
// xlRange.Font.Italic = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.Italic;
// xlRange.Font.Underline = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.Underline;
// xlRange.Font.FontStyle = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.FontFamily;
//}
worksheetcol += 1;
}
worksheetRow += 1;
}
}
public void ExportToExcel(List <PrintList> print)
{
// 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;
// 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"] = translationText.Get("excelA");
workSheet.Cells[1, "B"] = translationText.Get("excelB");
workSheet.Cells[1, "C"] = translationText.Get("excelC");
workSheet.Cells[1, "D"] = translationText.Get("excelD");
workSheet.Cells[1, "E"] = translationText.Get("excelE");
// ------------------------------------------------
// Populate sheet with some real data from "cars" list
// ------------------------------------------------
int row = 2; // start row (in row 1 are header cells)
foreach (PrintList prn in print)
{
workSheet.Cells[row, "A"] = prn.EmployeeName;
workSheet.Cells[row, "B"] = prn.DateTime;
workSheet.Cells[row, "C"] = prn.CtrlID;
workSheet.Cells[row, "D"] = prn.Ordinal;
workSheet.Cells[row, "E"] = prn.DepartmentName;
row++;
}
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
// Define filename
string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Save this data as a file
workSheet.SaveAs(fileName);
// Display SUCCESS message
Console.WriteLine(string.Format("The file '{0}' is saved successfully!", fileName));
//MessageBox.Show(string.Format("The file '{0}' is saved successfully!", fileName));
}
catch (Exception exception)
{
Console.WriteLine("Exception", "There was a PROBLEM saving Excel file!\n" + exception.Message);
//MessageBox.Show("Exception", "There was a PROBLEM saving Excel file!\n" + exception.Message, MessageBoxButtons.OK, essageBoxIcon.Error);
}
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 void KetXuatLuongNV(DataTable dt, string SheetName, string title)
{
//Tạo các đối tượng trong Excel
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks oBooks;
Microsoft.Office.Interop.Excel.Sheets oSheets;
Microsoft.Office.Interop.Excel.Workbook book;
Microsoft.Office.Interop.Excel.Worksheet sheet;
//tạo mới 1 Exel WorkBook
oExcel.Visible = true;
oExcel.DisplayAlerts = false;
oExcel.Application.SheetsInNewWorkbook = 1;
oBooks = oExcel.Workbooks;
book = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
oSheets = book.Worksheets;
sheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
sheet.Name = SheetName;
//tạo phần đầu nếu muốn
Microsoft.Office.Interop.Excel.Range head = sheet.get_Range("A1", "I1");
head.MergeCells = true;
head.Value2 = title;
head.Font.Bold = true;
head.Font.Name = "Tahoma";
head.Font.Size = "18";
head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//Tạo tiêu đề cột
Microsoft.Office.Interop.Excel.Range cl1 = sheet.get_Range("A3", "A3");
cl1.Value2 = "Mã NV";
cl1.ColumnWidth = 18.5;
Microsoft.Office.Interop.Excel.Range cl2 = sheet.get_Range("B3", "B3");
cl2.Value2 = "Họ tên";
cl2.ColumnWidth = 25.5;
Microsoft.Office.Interop.Excel.Range cl3 = sheet.get_Range("C3", "C3");
cl3.Value2 = "HSL";
cl3.ColumnWidth = 25.5;
Microsoft.Office.Interop.Excel.Range cl4 = sheet.get_Range("D3", "D99");
cl4.Value2 = "";
cl4.NumberFormat = "dd/MM/yyyy";
cl4.ColumnWidth = 33.5;
Microsoft.Office.Interop.Excel.Range cl5 = sheet.get_Range("E3", "E3");
cl5.Value2 = "BHXH";
cl5.ColumnWidth = 16;
Microsoft.Office.Interop.Excel.Range cl6 = sheet.get_Range("F3", "F3");
cl6.Value2 = "BHYT";
cl6.ColumnWidth = 16.0;
Microsoft.Office.Interop.Excel.Range cl7 = sheet.get_Range("G3", "G3");
cl7.Value2 = "BHTN";
cl7.ColumnWidth = 16;
Microsoft.Office.Interop.Excel.Range cl8 = sheet.get_Range("H3", "H3");
cl8.Value2 = "Tổng phụ cấp";
cl8.ColumnWidth = 16;
Microsoft.Office.Interop.Excel.Range cl9 = sheet.get_Range("I3", "I3");
cl9.Value2 = "Lương thực lãnh";
cl9.ColumnWidth = 30;
Microsoft.Office.Interop.Excel.Range rowHead = sheet.get_Range("A3", "I3");
rowHead.Font.Bold = true;
//kẻ viền
rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
//thiết lập màu nền
rowHead.Interior.ColorIndex = 15;
rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//tạo mảng đối tượng để lưu trữ toàn bộ dữ liệu trong Database
//vì dữ liệu đc gán vào các Cell trong Excel phải thông qua object thuần
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
//chuyển dữ liệu từ Database vào mảng đối tượng
for (int r = 0; r < dt.Rows.Count; r++)
{
DataRow dr = dt.Rows[r];
for (int c = 0; c < dt.Columns.Count; c++)
{
arr[r, c] = dr[c];
}
}
//thiết lập vùng điền dữ liệu
int rowStart = 4;
int columnStart = 1;
int rowEnd = rowStart + dt.Rows.Count - 1;
int columnEnd = dt.Columns.Count;
//Ô bắt đầu điền dữ liệu
Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowStart, columnStart];
//Ô kết thúc điền dữ liệu
Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowEnd, columnEnd];
//Lấy về vùng điền dữ liệu
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(c1, c2);
//Điền dữ liệu vào vùng đã thiết lập
range.Value2 = arr;
// Kẻ viền
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
// Căn giữa cột STT
Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowEnd, columnStart];
Microsoft.Office.Interop.Excel.Range c4 = sheet.get_Range(c1, c3);
sheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
