public static void SaveDataTableToExcel(DataTable excelTable, string filePath) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); try { app.Visible = false; Microsoft.Office.Interop.Excel.Workbook wBook = app.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet wSheet = wBook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet; if (excelTable.Rows.Count > 0) { int row = 0; row = excelTable.Rows.Count; int col = excelTable.Columns.Count; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { string str = excelTable.Rows[i][j].ToString(); wSheet.Cells[i + 2, j + 1] = str; } } } int size = excelTable.Columns.Count; for (int i = 0; i < size; i++) { wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName; } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); //保存excel文件 app.Save(filePath); app.SaveWorkspace(filePath); app.Quit(); app = null; } catch { throw; } }
public void GenerateExcelCurrent(System.Windows.Forms.DataGridView dataGridView1) { #region 导出EXCEL表格类 刘新阳2014年12月27日13:51:50 int columnCount = 0; //导出到execl try { //没有数据的话就不往下执行 if (dataGridView1.Rows.Count == 0) { throw new Exception("当前页面没有数据可以导出!"); } //实例化一个Excel.Application对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写 excel.Visible = false; //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错 excel.Application.Workbooks.Add(true); //生成Excel中列头名称,下面的不一定是大家都用得到的,我只是判断有哪些列是显示了的,然后只搬家这些列的数据。如果你是全部搬家,那就直接一个for循环就可以了------------------------------ for (int i = 0; i < dataGridView1.Columns.Count; i++) { if (dataGridView1.Columns[i].Visible) { columnCount = columnCount + 1; } } string[] headers = new string[columnCount]; int index = 0; for (int i = 0; i < dataGridView1.Columns.Count; i++) { if (dataGridView1.Columns[i].Visible) { headers[index] = dataGridView1.Columns[i].HeaderText; index = index + 1; } } for (int i = 0; i < columnCount; i++) { excel.Cells[1, i + 1] = headers[i]; if (dataGridView1.Columns[i].ValueType == typeof(DateTime)) { Microsoft.Office.Interop.Excel.Range headRange = excel.Cells[1, i - 1] as Microsoft.Office.Interop.Excel.Range; // as Range;//获取表头单元格 headRange.ColumnWidth = 22; //设置列宽 } } //---------------------------------------------------**********************************---------------------------------------- //把DataGridView当前页的数据保存在Excel中 for (int i = 0; i < dataGridView1.Rows.Count; i++) { int columnIndex = 0; for (int j = 0; j < dataGridView1.Columns.Count; j++) { if (dataGridView1.Columns[j].Visible) { columnIndex = columnIndex + 1; if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, columnIndex] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 2, columnIndex] = dataGridView1[j, i].Value.ToString(); } } } } //设置禁止弹出保存和覆盖的询问提示框 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; //保存工作簿 //excel.Application.Workbooks.Add(true).Save(); //保存excel文件 excel.Save(); //确保Excel进程关闭 excel.Quit(); excel = null; throw new Exception("当前页面数据已经成功导出到您指定的目录!"); } catch (Exception ex) { throw new Exception("操作错误,请联系管理员", ex); } #endregion }
public static void ExportExcel_CTCCByGroup(DataTable dt, DataTable dtpb, DataTable dtgroup, int pos_row, string pos_column, bool IsOpen, string sTemplateFile, string sfilename, bool Show_Column) { Microsoft.Office.Interop.Excel.Application ReportFile = new Microsoft.Office.Interop.Excel.Application(); ReportFile.Visible = false; Microsoft.Office.Interop.Excel._Workbook WorkBook; //Microsoft.Office.Interop.Excel.Range chartRange; try { if (sfilename != "") { if (File.Exists(sfilename)) { File.Copy(sTemplateFile, sfilename, true); } else { File.Copy(sTemplateFile, sfilename); } WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Open(sfilename, 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)); } else { WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Add(sTemplateFile)); } Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.ActiveSheet; // xu ly xuat theo phong ban // begin for phong ban DataView dtView = new DataView(dt); DataView dtViewGroup = new DataView(dtgroup); for (int ii = 0; ii < dtpb.Rows.Count; ii++) { dtView.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "'"; // kiem tra trong du lieu group xem co phong ban nay ko dtViewGroup.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "'"; if (dtViewGroup.Count > 0) { #region Tim Thay Du lieu for (int tt = 0; tt < dtViewGroup.Count; tt++) { dtView.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "' And GroupName = '" + dtViewGroup[tt]["GroupName"].ToString() + "'"; //Tinh so luong cot xuat ra string MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int record_count = dtView.Count; int record_mod = record_count; string pos_begin1 = pos_column; int pos_begin2 = pos_row; object[,] Array_Record; string Cell_Begin, Cell_End; Cell_Begin = pos_begin1 + (pos_begin2).ToString(); Cell_End = MaxColumn + (record_count + pos_row - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + (record_mod + pos_row).ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dtView[l][m]; } // SET MAU KHONG CHAM CONG //if (Array_Record[l, 9] == DBNull.Value) //{ // // Microsoft.Office.Interop.Excel.Range Range = Sheet.get_Range("J" + l, "J" + l); // // Range.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb(); //} //if (Array_Record[l, 10] == DBNull.Value) //{ // // Microsoft.Office.Interop.Excel.Range Range = Sheet.get_Range("K" + l, "K" + l); // // Range.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb(); //} } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; Sheet.Name = dtViewGroup[tt]["GroupName"].ToString(); Sheet.Copy(Sheet); // clrear txt Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = ""; } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 0; //Sheet.get_Range(Cell_Begin, Cell_End).Interior.Color = System.Drawing.Color.Transparent.ToArgb(); // end cler text in excel } #endregion // xu ly voi du lieu chi phong ban dtView.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "' And GroupName is null"; if (dtView.Count > 0) { //Tinh so luong cot xuat ra string _MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { _MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int _record_count = dtView.Count; int _record_mod = _record_count; string _pos_begin1 = pos_column; int _pos_begin2 = pos_row; object[,] _Array_Record; string _Cell_Begin, _Cell_End; _Cell_Begin = _pos_begin1 + (_pos_begin2).ToString(); _Cell_End = _MaxColumn + (_record_count + pos_row - 1).ToString(); _Array_Record = new object[_record_mod, dt.Columns.Count]; for (int l = 0; l < _record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { _Array_Record[l, m] = dtView[l][m]; } // SET MAU KHONG CHAM CONG //if (_Array_Record[l, 9].ToString() == "") //{ // chartRange = Sheet.get_Range("J" + l + pos_row, Type.Missing); // chartRange.Interior.Color = System.Drawing.Color.Blue.ToArgb(); //} //if (_Array_Record[l, 10].ToString() == "") //{ // chartRange = Sheet.get_Range("K" + l + pos_row, Type.Missing); // chartRange.Interior.Color = System.Drawing.Color.Blue.ToArgb(); //} } Sheet.get_Range(_Cell_Begin, _Cell_End).Value2 = _Array_Record; Sheet.get_Range(_Cell_Begin, _Cell_End).Borders.LineStyle = 1; Sheet.Name = dtpb.Rows[ii]["DepartmentName"].ToString(); Sheet.Copy(Sheet); // clrear txt _Array_Record = new object[_record_mod, dt.Columns.Count]; for (int l = 0; l < _record_mod; l++) for (int m = 0; m < dt.Columns.Count; m++) { _Array_Record[l, m] = ""; } Sheet.get_Range(_Cell_Begin, _Cell_End).Value2 = _Array_Record; Sheet.get_Range(_Cell_Begin, _Cell_End).Borders.LineStyle = 0; //Sheet.get_Range(_Cell_Begin, _Cell_End).Interior.Color = System.Drawing.Color.Transparent.ToArgb(); // end cler text in excel } // Het xu ly voi du lieu chi phong ban } else { //Tinh so luong cot xuat ra string MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int record_count = dtView.Count; int record_mod = record_count; string pos_begin1 = pos_column; int pos_begin2 = pos_row; object[,] Array_Record; string Cell_Begin, Cell_End; Cell_Begin = pos_begin1 + (pos_begin2).ToString(); Cell_End = MaxColumn + (record_count + pos_row - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + (record_mod + pos_row).ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dtView[l][m]; } // SET MAU KHONG CHAM CONG //if (Array_Record[l, 9].ToString()== "") //{ // chartRange = Sheet.get_Range("J" + l+pos_row, Type.Missing); // chartRange.Interior.Color = System.Drawing.Color.Blue.ToArgb(); //} //if (Array_Record[l, 10].ToString() == "") //{ // chartRange = Sheet.get_Range("K" + l + pos_row, Type.Missing); // chartRange.Interior.Color = System.Drawing.Color.Blue.ToArgb(); //} } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; Sheet.Name = dtpb.Rows[ii]["DepartmentName"].ToString(); Sheet.Copy(Sheet); // clrear txt Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = ""; } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 0; //Sheet.get_Range(Cell_Begin, Cell_End).Interior.Color = System.Drawing.Color.Transparent.ToArgb(); // end cler text in excel } } // end for phong ban for (int iii = 1; iii < WorkBook.Worksheets.Count; iii++) // xóa số (2) o sheet name { Microsoft.Office.Interop.Excel._Worksheet SheetName = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Worksheets[iii]; string _sheetname = SheetName.Name; _sheetname = _sheetname.Replace("(2)", ""); SheetName.Name = _sheetname; } //save file if (sfilename == "") { ReportFile.AlertBeforeOverwriting = false; ReportFile.DisplayAlerts = false; ReportFile.Save(Type.Missing); MessageBox.Show("Export Excel Successful !!!"); } else { //if (IsOpen) //{ // IsOpen = false; //} WorkBook.Save(); // MessageBox.Show("Export Excel Successful !!!"); } if (IsOpen) { ReportFile.Visible = true; ReportFile.UserControl = true; } //ReportFile.Quit(); releaseObject(Sheet); releaseObject(WorkBook); releaseObject(ReportFile); //foreach (Process process in Process.GetProcessesByName("EXCEL")) //{ // process.Kill(); //} } catch (Exception exx) { string Error = exx.ToString(); } }
/// <summary> /// Ghi chú /// </summary> /// <param name="dt">Bảng dữ liệu sẽ xuất ra excel</param> /// <param name="pos_row">xuất ra excel từ vị trí dòng này</param> /// <param name="pos_column">xuất ra excel từ vị trí cột này</param> /// <param name="IsOpen">Xuất xong có mở File ra xem hay không</param> /// <param name="sTemplateFile">Đường dẫn đến File Template</param> /// <param name="sfilename">Đường dẫn nơi sẽ lưu file</param> /// <param name="Show_Column">Có xuất tên cột ra hay không</param> public static void ExportExcel(DataTable dt, int pos_row, string pos_column, bool IsOpen, string sTemplateFile, string sfilename, bool Show_Column) { try { Microsoft.Office.Interop.Excel.Application ReportFile = new Microsoft.Office.Interop.Excel.Application(); ReportFile.Visible = false; Microsoft.Office.Interop.Excel._Workbook WorkBook; if (sfilename != "") { if (File.Exists(sfilename)) { // Configure message box string message = "File đã tồn tại.Bạn muốn lưu chồng lên nó?"; string caption = "Thông Báo"; MessageBoxButtons buttons = MessageBoxButtons.YesNo; MessageBoxIcon icon = MessageBoxIcon.Question; // Show message box if (MessageBox.Show(message, caption, buttons, icon, MessageBoxDefaultButton.Button2) == DialogResult.Yes) { File.Copy(sTemplateFile, sfilename, true); } } else { File.Copy(sTemplateFile, sfilename); } WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Open(sfilename, 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)); } else { WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Add(sTemplateFile)); } Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.ActiveSheet; // Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Sheets; //Tinh so luong cot xuat ra string MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int record_count = dt.Rows.Count; int record_div = dt.Rows.Count / 1000; int record_mod = dt.Rows.Count % 1000; string pos_begin1 = pos_column; int pos_begin2 = pos_row; int Buffer_Size = 100; #region move footer //move footer Microsoft.Office.Interop.Excel.Range srcRange = Sheet.get_Range("A" + (pos_row + 1).ToString(), "AZ" + (pos_row + Buffer_Size + 1).ToString()); srcRange.Copy(Type.Missing); string Cell21 = "A" + (record_count + pos_row + 1 + Buffer_Size).ToString(); string Cell22 = "AZ" + (record_count + pos_row + 2 * Buffer_Size).ToString(); Microsoft.Office.Interop.Excel.Range destRange = Sheet.get_Range(Cell21, Cell22); Sheet.Paste(destRange, Type.Missing); srcRange.Rows.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); //end move footer #endregion //Xuat cot mac dinh trong table neu Show_Column == true if (Show_Column == true) { CreateColumnName(dt, Sheet, pos_row); pos_begin2 = pos_row + 1; } object[,] Array_Record; string Cell_Begin, Cell_End; if (record_div == 0) //case record_count<1000 { Cell_Begin = pos_begin1 + (pos_begin2).ToString(); Cell_End = MaxColumn + (record_mod + pos_row - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + (record_mod + pos_row).ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dt.Rows[l].ItemArray[m]; } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; } else //case record_count>1000 { int pos_end = 1000 + pos_row; int temp = 0; Cell_Begin = pos_begin1 + pos_begin2.ToString(); Cell_End = MaxColumn + (pos_end).ToString(); for (int i = 0; i < record_div; i++) { Array_Record = new object[1000, dt.Columns.Count]; for (int k = temp, l = 0; k < temp + 1000; k++, l++) for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dt.Rows[k].ItemArray[m]; } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; if (i < record_div - 1) { temp += 1000; pos_end += 1000; pos_begin2 += 1000; Cell_Begin = pos_begin1 + pos_begin2.ToString(); Cell_End = MaxColumn + pos_end.ToString(); } } temp += 1000; pos_end += record_mod; pos_begin2 += 1000; Cell_Begin = pos_begin1 + pos_begin2.ToString(); Cell_End = MaxColumn + (pos_end - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + pos_end.ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dt.Rows[temp + l].ItemArray[m]; } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; } //save file if (sfilename == "") { ReportFile.AlertBeforeOverwriting = false; ReportFile.DisplayAlerts = false; ReportFile.Save(Type.Missing); MessageBox.Show("Export Excel Successful !!!"); } else { //if (IsOpen) //{ // IsOpen = false; //} WorkBook.Save(); MessageBox.Show("Export Excel Successful !!!"); } if (IsOpen) { ReportFile.Visible = true; ReportFile.UserControl = true; } //ReportFile.Quit(); releaseObject(Sheet); releaseObject(WorkBook); releaseObject(ReportFile); //foreach (Process process in Process.GetProcessesByName("EXCEL")) //{ // process.Kill(); //} } catch (Exception exx) { string Error = exx.ToString(); } }
public static void ExportExcel_CTCC(DataTable dt, DataTable dtpb, int pos_row, string pos_column, bool IsOpen, string sTemplateFile, string sfilename, bool Show_Column) { Microsoft.Office.Interop.Excel.Application ReportFile = new Microsoft.Office.Interop.Excel.Application(); ReportFile.Visible = false; Microsoft.Office.Interop.Excel._Workbook WorkBook; try { if (sfilename != "") { if (File.Exists(sfilename)) { //// Configure message box //string message = "File đã tồn tại.Bạn muốn lưu chồng lên nó?"; //string caption = "Thông Báo"; //MessageBoxButtons buttons = MessageBoxButtons.YesNo; //MessageBoxIcon icon = MessageBoxIcon.Question; //// Show message box //if (MessageBox.Show(message, caption, buttons, icon, MessageBoxDefaultButton.Button2) == DialogResult.Yes) //{ File.Copy(sTemplateFile, sfilename, true); //} } else { File.Copy(sTemplateFile, sfilename); } WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Open(sfilename, 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)); } else { WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Add(sTemplateFile)); } Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.ActiveSheet; // xu ly xuat theo phong ban // begin for phong ban DataView dtView = new DataView(dt); for (int ii = 0; ii < dtpb.Rows.Count; ii++) { dtView.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "'"; // Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Sheets; //Tinh so luong cot xuat ra string MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int record_count = dtView.Count; int record_mod = record_count; string pos_begin1 = pos_column; int pos_begin2 = pos_row; #region move footer //int Buffer_Size = 100; ////move footer //Microsoft.Office.Interop.Excel.Range srcRange = Sheet.get_Range("A" + (pos_row + 1).ToString(), "AZ" + (pos_row + Buffer_Size + 1).ToString()); //srcRange.Copy(Type.Missing); //string Cell21 = "A" + (record_count + pos_row + 1 + Buffer_Size).ToString(); //string Cell22 = "AZ" + (record_count + pos_row + 2 * Buffer_Size).ToString(); //Microsoft.Office.Interop.Excel.Range destRange = Sheet.get_Range(Cell21, Cell22); //Sheet.Paste(destRange, Type.Missing); //srcRange.Rows.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); //end move footer #endregion //Xuat cot mac dinh trong table neu Show_Column == true if (Show_Column == true) { CreateColumnName(dt, Sheet, pos_row); pos_begin2 = pos_row + 1; } object[,] Array_Record; string Cell_Begin, Cell_End; Cell_Begin = pos_begin1 + (pos_begin2).ToString(); Cell_End = MaxColumn + (record_count + pos_row - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + (record_mod + pos_row).ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dtView[l][m]; } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; //for (int l = 0; l < record_mod; l++) // for (int m = 0; m < dt.Columns.Count; m++) // { // if (m == 9) // { // if (Array_Record[l, m]==DBNull.Value) // { // Sheet.get_Range("J"+l).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkOrange); // } // } // } // copy sheet to new // Sheet.Copy(WorkBook.Sheets[0], WorkBook.Sheets[1]); //Microsoft.Office.Interop.Excel._Worksheet Sheet2 = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Worksheets[2]; Sheet.Name = dtpb.Rows[ii]["DepartmentName"].ToString(); Sheet.Copy(Sheet); // clrear txt Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = ""; } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 0; // end cler text in excel } // end for phong ban for (int iii = 1; iii < WorkBook.Worksheets.Count; iii++) // xóa số (2) o sheet name { Microsoft.Office.Interop.Excel._Worksheet SheetName= (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Worksheets[iii]; string _sheetname = SheetName.Name; _sheetname = _sheetname.Replace("(2)", ""); SheetName.Name = _sheetname; } //save file if (sfilename == "") { ReportFile.AlertBeforeOverwriting = false; ReportFile.DisplayAlerts = false; ReportFile.Save(Type.Missing); MessageBox.Show("Export Excel Successful !!!"); } else { //if (IsOpen) //{ // IsOpen = false; //} WorkBook.Save(); // MessageBox.Show("Export Excel Successful !!!"); } if (IsOpen) { ReportFile.Visible = true; ReportFile.UserControl = true; } //ReportFile.Quit(); releaseObject(Sheet); releaseObject(WorkBook); releaseObject(ReportFile); //foreach (Process process in Process.GetProcessesByName("EXCEL")) //{ // process.Kill(); //} } catch (Exception ex) { Class.App.Log_Write(ex.Message); } }
/// <summary> /// Ghi chú /// </summary> /// <param name="dt">Bảng dữ liệu sẽ xuất ra excel</param> /// <param name="pos_row">xuất ra excel từ vị trí dòng này</param> /// <param name="pos_column">xuất ra excel từ vị trí cột này</param> /// <param name="IsOpen">Xuất xong có mở File ra xem hay không</param> /// <param name="sTemplateFile">Đường dẫn đến File Template</param> /// <param name="sfilename">Đường dẫn nơi sẽ lưu file</param> /// <param name="Show_Column">Có xuất tên cột ra hay không</param> public static void ExportExcel(DataTable dt, int pos_row, string pos_column, bool IsOpen, string sTemplateFile, string sfilename, bool Show_Column) { try { Microsoft.Office.Interop.Excel.Application ReportFile = new Microsoft.Office.Interop.Excel.Application(); ReportFile.Visible = false; Microsoft.Office.Interop.Excel._Workbook WorkBook; if (sfilename != "") { if (File.Exists(sfilename)) { // Configure message box string message = "File đã tồn tại.Bạn muốn lưu chồng lên nó?"; string caption = "Thông Báo"; MessageBoxButtons buttons = MessageBoxButtons.YesNo; MessageBoxIcon icon = MessageBoxIcon.Question; // Show message box if (MessageBox.Show(message, caption, buttons, icon, MessageBoxDefaultButton.Button2) == DialogResult.Yes) { File.Copy(sTemplateFile, sfilename, true); } } else { File.Copy(sTemplateFile, sfilename); } WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Open(sfilename, 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)); } else { WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Add(sTemplateFile)); } Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.ActiveSheet; // Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Sheets; //Tinh so luong cot xuat ra string MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int record_count = dt.Rows.Count; int record_div = dt.Rows.Count / 1000; int record_mod = dt.Rows.Count % 1000; string pos_begin1 = pos_column; int pos_begin2 = pos_row; int Buffer_Size = 100; #region move footer //move footer Microsoft.Office.Interop.Excel.Range srcRange = Sheet.get_Range("A" + (pos_row + 1).ToString(), "AZ" + (pos_row + Buffer_Size + 1).ToString()); srcRange.Copy(Type.Missing); string Cell21 = "A" + (record_count + pos_row + 1 + Buffer_Size).ToString(); string Cell22 = "AZ" + (record_count + pos_row + 2 * Buffer_Size).ToString(); Microsoft.Office.Interop.Excel.Range destRange = Sheet.get_Range(Cell21, Cell22); Sheet.Paste(destRange, Type.Missing); srcRange.Rows.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); //end move footer #endregion //Xuat cot mac dinh trong table neu Show_Column == true if (Show_Column == true) { CreateColumnName(dt, Sheet, pos_row); pos_begin2 = pos_row + 1; } object[,] Array_Record; string Cell_Begin, Cell_End; if (record_div == 0) //case record_count<1000 { Cell_Begin = pos_begin1 + (pos_begin2).ToString(); Cell_End = MaxColumn + (record_mod + pos_row - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + (record_mod + pos_row).ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dt.Rows[l].ItemArray[m]; } } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; } else //case record_count>1000 { int pos_end = 1000 + pos_row; int temp = 0; Cell_Begin = pos_begin1 + pos_begin2.ToString(); Cell_End = MaxColumn + (pos_end).ToString(); for (int i = 0; i < record_div; i++) { Array_Record = new object[1000, dt.Columns.Count]; for (int k = temp, l = 0; k < temp + 1000; k++, l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dt.Rows[k].ItemArray[m]; } } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; if (i < record_div - 1) { temp += 1000; pos_end += 1000; pos_begin2 += 1000; Cell_Begin = pos_begin1 + pos_begin2.ToString(); Cell_End = MaxColumn + pos_end.ToString(); } } temp += 1000; pos_end += record_mod; pos_begin2 += 1000; Cell_Begin = pos_begin1 + pos_begin2.ToString(); Cell_End = MaxColumn + (pos_end - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + pos_end.ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dt.Rows[temp + l].ItemArray[m]; } } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; } //save file if (sfilename == "") { ReportFile.AlertBeforeOverwriting = false; ReportFile.DisplayAlerts = false; ReportFile.Save(Type.Missing); MessageBox.Show("Export Excel Successful !!!"); } else { //if (IsOpen) //{ // IsOpen = false; //} WorkBook.Save(); MessageBox.Show("Export Excel Successful !!!"); } if (IsOpen) { ReportFile.Visible = true; ReportFile.UserControl = true; } //ReportFile.Quit(); releaseObject(Sheet); releaseObject(WorkBook); releaseObject(ReportFile); //foreach (Process process in Process.GetProcessesByName("EXCEL")) //{ // process.Kill(); //} } catch (Exception exx) { string Error = exx.ToString(); } }
public static void ExportExcel_CTCCByGroup(DataTable dt, DataTable dtpb, DataTable dtgroup, int pos_row, string pos_column, bool IsOpen, string sTemplateFile, string sfilename, bool Show_Column) { Microsoft.Office.Interop.Excel.Application ReportFile = new Microsoft.Office.Interop.Excel.Application(); ReportFile.Visible = false; Microsoft.Office.Interop.Excel._Workbook WorkBook; //Microsoft.Office.Interop.Excel.Range chartRange; try { if (sfilename != "") { if (File.Exists(sfilename)) { File.Copy(sTemplateFile, sfilename, true); } else { File.Copy(sTemplateFile, sfilename); } WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Open(sfilename, 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)); } else { WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Add(sTemplateFile)); } Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.ActiveSheet; // xu ly xuat theo phong ban // begin for phong ban DataView dtView = new DataView(dt); DataView dtViewGroup = new DataView(dtgroup); for (int ii = 0; ii < dtpb.Rows.Count; ii++) { dtView.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "'"; // kiem tra trong du lieu group xem co phong ban nay ko dtViewGroup.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "'"; if (dtViewGroup.Count > 0) { #region Tim Thay Du lieu for (int tt = 0; tt < dtViewGroup.Count; tt++) { dtView.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "' And GroupName = '" + dtViewGroup[tt]["GroupName"].ToString() + "'"; //Tinh so luong cot xuat ra string MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int record_count = dtView.Count; int record_mod = record_count; string pos_begin1 = pos_column; int pos_begin2 = pos_row; object[,] Array_Record; string Cell_Begin, Cell_End; Cell_Begin = pos_begin1 + (pos_begin2).ToString(); Cell_End = MaxColumn + (record_count + pos_row - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + (record_mod + pos_row).ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dtView[l][m]; } // SET MAU KHONG CHAM CONG //if (Array_Record[l, 9] == DBNull.Value) //{ // // Microsoft.Office.Interop.Excel.Range Range = Sheet.get_Range("J" + l, "J" + l); // // Range.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb(); //} //if (Array_Record[l, 10] == DBNull.Value) //{ // // Microsoft.Office.Interop.Excel.Range Range = Sheet.get_Range("K" + l, "K" + l); // // Range.Cells.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb(); //} } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; Sheet.Name = dtViewGroup[tt]["GroupName"].ToString(); Sheet.Copy(Sheet); // clrear txt Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = ""; } } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 0; //Sheet.get_Range(Cell_Begin, Cell_End).Interior.Color = System.Drawing.Color.Transparent.ToArgb(); // end cler text in excel } #endregion // xu ly voi du lieu chi phong ban dtView.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "' And GroupName is null"; if (dtView.Count > 0) { //Tinh so luong cot xuat ra string _MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { _MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int _record_count = dtView.Count; int _record_mod = _record_count; string _pos_begin1 = pos_column; int _pos_begin2 = pos_row; object[,] _Array_Record; string _Cell_Begin, _Cell_End; _Cell_Begin = _pos_begin1 + (_pos_begin2).ToString(); _Cell_End = _MaxColumn + (_record_count + pos_row - 1).ToString(); _Array_Record = new object[_record_mod, dt.Columns.Count]; for (int l = 0; l < _record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { _Array_Record[l, m] = dtView[l][m]; } // SET MAU KHONG CHAM CONG //if (_Array_Record[l, 9].ToString() == "") //{ // chartRange = Sheet.get_Range("J" + l + pos_row, Type.Missing); // chartRange.Interior.Color = System.Drawing.Color.Blue.ToArgb(); //} //if (_Array_Record[l, 10].ToString() == "") //{ // chartRange = Sheet.get_Range("K" + l + pos_row, Type.Missing); // chartRange.Interior.Color = System.Drawing.Color.Blue.ToArgb(); //} } Sheet.get_Range(_Cell_Begin, _Cell_End).Value2 = _Array_Record; Sheet.get_Range(_Cell_Begin, _Cell_End).Borders.LineStyle = 1; Sheet.Name = dtpb.Rows[ii]["DepartmentName"].ToString(); Sheet.Copy(Sheet); // clrear txt _Array_Record = new object[_record_mod, dt.Columns.Count]; for (int l = 0; l < _record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { _Array_Record[l, m] = ""; } } Sheet.get_Range(_Cell_Begin, _Cell_End).Value2 = _Array_Record; Sheet.get_Range(_Cell_Begin, _Cell_End).Borders.LineStyle = 0; //Sheet.get_Range(_Cell_Begin, _Cell_End).Interior.Color = System.Drawing.Color.Transparent.ToArgb(); // end cler text in excel } // Het xu ly voi du lieu chi phong ban } else { //Tinh so luong cot xuat ra string MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int record_count = dtView.Count; int record_mod = record_count; string pos_begin1 = pos_column; int pos_begin2 = pos_row; object[,] Array_Record; string Cell_Begin, Cell_End; Cell_Begin = pos_begin1 + (pos_begin2).ToString(); Cell_End = MaxColumn + (record_count + pos_row - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + (record_mod + pos_row).ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dtView[l][m]; } // SET MAU KHONG CHAM CONG //if (Array_Record[l, 9].ToString()== "") //{ // chartRange = Sheet.get_Range("J" + l+pos_row, Type.Missing); // chartRange.Interior.Color = System.Drawing.Color.Blue.ToArgb(); //} //if (Array_Record[l, 10].ToString() == "") //{ // chartRange = Sheet.get_Range("K" + l + pos_row, Type.Missing); // chartRange.Interior.Color = System.Drawing.Color.Blue.ToArgb(); //} } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; Sheet.Name = dtpb.Rows[ii]["DepartmentName"].ToString(); Sheet.Copy(Sheet); // clrear txt Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = ""; } } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 0; //Sheet.get_Range(Cell_Begin, Cell_End).Interior.Color = System.Drawing.Color.Transparent.ToArgb(); // end cler text in excel } } // end for phong ban for (int iii = 1; iii < WorkBook.Worksheets.Count; iii++) // xóa số (2) o sheet name { Microsoft.Office.Interop.Excel._Worksheet SheetName = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Worksheets[iii]; string _sheetname = SheetName.Name; _sheetname = _sheetname.Replace("(2)", ""); SheetName.Name = _sheetname; } //save file if (sfilename == "") { ReportFile.AlertBeforeOverwriting = false; ReportFile.DisplayAlerts = false; ReportFile.Save(Type.Missing); MessageBox.Show("Export Excel Successful !!!"); } else { //if (IsOpen) //{ // IsOpen = false; //} WorkBook.Save(); // MessageBox.Show("Export Excel Successful !!!"); } if (IsOpen) { ReportFile.Visible = true; ReportFile.UserControl = true; } //ReportFile.Quit(); releaseObject(Sheet); releaseObject(WorkBook); releaseObject(ReportFile); //foreach (Process process in Process.GetProcessesByName("EXCEL")) //{ // process.Kill(); //} } catch (Exception exx) { string Error = exx.ToString(); } }
public static void ExportExcel_CTCC(DataTable dt, DataTable dtpb, int pos_row, string pos_column, bool IsOpen, string sTemplateFile, string sfilename, bool Show_Column) { Microsoft.Office.Interop.Excel.Application ReportFile = new Microsoft.Office.Interop.Excel.Application(); ReportFile.Visible = false; Microsoft.Office.Interop.Excel._Workbook WorkBook; try { if (sfilename != "") { if (File.Exists(sfilename)) { //// Configure message box //string message = "File đã tồn tại.Bạn muốn lưu chồng lên nó?"; //string caption = "Thông Báo"; //MessageBoxButtons buttons = MessageBoxButtons.YesNo; //MessageBoxIcon icon = MessageBoxIcon.Question; //// Show message box //if (MessageBox.Show(message, caption, buttons, icon, MessageBoxDefaultButton.Button2) == DialogResult.Yes) //{ File.Copy(sTemplateFile, sfilename, true); //} } else { File.Copy(sTemplateFile, sfilename); } WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Open(sfilename, 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)); } else { WorkBook = (Microsoft.Office.Interop.Excel._Workbook)(ReportFile.Workbooks.Add(sTemplateFile)); } Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.ActiveSheet; // xu ly xuat theo phong ban // begin for phong ban DataView dtView = new DataView(dt); for (int ii = 0; ii < dtpb.Rows.Count; ii++) { dtView.RowFilter = "DepartmentName = '" + dtpb.Rows[ii]["DepartmentName"].ToString() + "'"; // Microsoft.Office.Interop.Excel._Worksheet Sheet = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Sheets; //Tinh so luong cot xuat ra string MaxColumn = ((String)(Convert.ToChar(dt.Columns.Count / 26 + 64).ToString() + Convert.ToChar(dt.Columns.Count % 26 + 64))).Replace('@', ' ').Trim(); if (dt.Columns.Count % 26 == 0) { MaxColumn = ((String)(Convert.ToChar((dt.Columns.Count) / 26 + 64).ToString() + Convert.ToChar((dt.Columns.Count + 1) % 26 + 64))).Replace('@', ' ').Trim(); } //cac tham so int record_count = dtView.Count; int record_mod = record_count; string pos_begin1 = pos_column; int pos_begin2 = pos_row; #region move footer //int Buffer_Size = 100; ////move footer //Microsoft.Office.Interop.Excel.Range srcRange = Sheet.get_Range("A" + (pos_row + 1).ToString(), "AZ" + (pos_row + Buffer_Size + 1).ToString()); //srcRange.Copy(Type.Missing); //string Cell21 = "A" + (record_count + pos_row + 1 + Buffer_Size).ToString(); //string Cell22 = "AZ" + (record_count + pos_row + 2 * Buffer_Size).ToString(); //Microsoft.Office.Interop.Excel.Range destRange = Sheet.get_Range(Cell21, Cell22); //Sheet.Paste(destRange, Type.Missing); //srcRange.Rows.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp); //end move footer #endregion //Xuat cot mac dinh trong table neu Show_Column == true if (Show_Column == true) { CreateColumnName(dt, Sheet, pos_row); pos_begin2 = pos_row + 1; } object[,] Array_Record; string Cell_Begin, Cell_End; Cell_Begin = pos_begin1 + (pos_begin2).ToString(); Cell_End = MaxColumn + (record_count + pos_row - 1).ToString(); if (Show_Column == true) { Cell_End = MaxColumn + (record_mod + pos_row).ToString(); } Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = dtView[l][m]; } } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 1; //for (int l = 0; l < record_mod; l++) // for (int m = 0; m < dt.Columns.Count; m++) // { // if (m == 9) // { // if (Array_Record[l, m]==DBNull.Value) // { // Sheet.get_Range("J"+l).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkOrange); // } // } // } // copy sheet to new // Sheet.Copy(WorkBook.Sheets[0], WorkBook.Sheets[1]); //Microsoft.Office.Interop.Excel._Worksheet Sheet2 = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Worksheets[2]; Sheet.Name = dtpb.Rows[ii]["DepartmentName"].ToString(); Sheet.Copy(Sheet); // clrear txt Array_Record = new object[record_mod, dt.Columns.Count]; for (int l = 0; l < record_mod; l++) { for (int m = 0; m < dt.Columns.Count; m++) { Array_Record[l, m] = ""; } } Sheet.get_Range(Cell_Begin, Cell_End).Value2 = Array_Record; Sheet.get_Range(Cell_Begin, Cell_End).Borders.LineStyle = 0; // end cler text in excel } // end for phong ban for (int iii = 1; iii < WorkBook.Worksheets.Count; iii++) // xóa số (2) o sheet name { Microsoft.Office.Interop.Excel._Worksheet SheetName = (Microsoft.Office.Interop.Excel._Worksheet)WorkBook.Worksheets[iii]; string _sheetname = SheetName.Name; _sheetname = _sheetname.Replace("(2)", ""); SheetName.Name = _sheetname; } //save file if (sfilename == "") { ReportFile.AlertBeforeOverwriting = false; ReportFile.DisplayAlerts = false; ReportFile.Save(Type.Missing); MessageBox.Show("Export Excel Successful !!!"); } else { //if (IsOpen) //{ // IsOpen = false; //} WorkBook.Save(); // MessageBox.Show("Export Excel Successful !!!"); } if (IsOpen) { ReportFile.Visible = true; ReportFile.UserControl = true; } //ReportFile.Quit(); releaseObject(Sheet); releaseObject(WorkBook); releaseObject(ReportFile); //foreach (Process process in Process.GetProcessesByName("EXCEL")) //{ // process.Kill(); //} } catch (Exception ex) { Class.App.Log_Write(ex.Message); } }
public void ToExcel(DataGridView dataGridView1) { try { //没有数据的话就不往下执行 if (dataGridView1.Rows.Count == 0) { return; } //实例化一个Excel.Application对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写 excel.Visible = true; //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错 excel.Application.Workbooks.Add(true); //生成Excel中列头名称 for (int i = 0; i < dataGridView1.Columns.Count; i++) { if (dataGridView1.Columns[i].Visible == true) { excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; } } //把DataGridView当前页的数据保存在Excel中 for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { System.Windows.Forms.Application.DoEvents(); for (int j = 0; j < dataGridView1.Columns.Count; j++) { if (this.skinDataGridView1.Columns[j].Visible == true) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString(); } } } } //设置禁止弹出保存和覆盖的询问提示框 excel.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; //保存工作簿 excel.Application.Workbooks.Add(true).Save(); //保存excel文件 excel.Save("D:" + "\\KKHMD.xls"); //确保Excel进程关闭 excel.Quit(); excel = null; GC.Collect();//如果不使用这条语句会导致excel进程无法正常退出,使用后正常退出 MessageBox.Show(this, "文件已经成功导出!", "信息提示"); } catch (Exception ex) { MessageBox.Show(ex.Message, "错误提示"); } }
public void ToExcel(DataGridView dataGridView1) { //string fileName = "yk" + DateTime.Now.Ticks; //if (myDGV.Rows.Count > 0) //{ // string saveFileName = ""; // bool fileSaved = false; // 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 // 写入标题 // for (int i = 0; i < myDGV.ColumnCount; i++) // { // worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText; // } // this.pb.Visible = true; // this.pb.Maximum = myDGV.Rows.Count * myDGV.ColumnCount; // this.pb.Value = 0; // int ab = 0; // 写入数值 // for (int r = 0; r < myDGV.Rows.Count; r++) // { // for (int i = 0; i < myDGV.ColumnCount; i++) // { // this.pb.Increment(ab++); // worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value; // } // System.Windows.Forms.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 (saveFileName != "") // { // try // { // workbook.Saved = true; // workbook.SaveCopyAs(saveFileName); // fileSaved = true; // } // catch (Exception ex) // { // fileSaved = false; // MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message); // } // } // else // { // fileSaved = false; // } // xlApp.Quit(); // GC.Collect();//强行销毁 // this.pb.Maximum = 100; // this.pb.Visible = false; // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL // MessageBox.Show(fileName + "的简明资料保存成功", "提示", MessageBoxButtons.OK); //} //else //{ // MessageBox.Show("报表为空,无表格需要导出", "提示", MessageBoxButtons.OK); //} try { //没有数据的话就不往下执行 if (dataGridView1.Rows.Count == 0) { return; } //实例化一个Excel.Application对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写 excel.Visible = true; //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错 excel.Application.Workbooks.Add(true); //生成Excel中列头名称 for (int i = 0; i < dataGridView1.Columns.Count; i++) { if (this.dt_result.Columns[i].Visible == true) { excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; } } //把DataGridView当前页的数据保存在Excel中 for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { System.Windows.Forms.Application.DoEvents(); for (int j = 0; j < dataGridView1.Columns.Count; j++) { if (this.dt_result.Columns[j].Visible == true) { if (dataGridView1[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString(); } } } } //设置禁止弹出保存和覆盖的询问提示框 excel.DisplayAlerts = true; excel.AlertBeforeOverwriting = true; //保存工作簿 excel.Application.Workbooks.Add(true).Save(); //保存excel文件 excel.Save("D:" + "\\yc" + DateTime.Now.Ticks + ".xls"); //确保Excel进程关闭 excel.Quit(); excel = null; GC.Collect();//如果不使用这条语句会导致excel进程无法正常退出,使用后正常退出 MessageBox.Show(this, "文件已经成功导出!", "信息提示"); } catch (Exception ex) { MessageBox.Show(ex.Message, "错误提示"); } }