public void copyPasteRangeExcel(string cellrng1, string cellrng2)
{
Microsoft.Office.Interop.Excel.Range sourceRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A17", "A17").EntireRow;
Microsoft.Office.Interop.Excel.Range destinationRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range(cellrng1, cellrng2);
sourceRange.Copy(Type.Missing);
// destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
// destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType., Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//excelApp.get_Range("A1:A360,B1:E1", Type.Missing).Merge(Type.Missing);
excelWorkbook.Save();
// excelWorkbook.Close(true, false, Type.Missing);
}
/// <summary>
/// 复制行（在指定行下面复制指定数量行）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void CopyRows(int rowIndex, int count)
{
try
{
range1 = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[rowIndex, this.missing];
for (int i = 1; i <= count; i++)
{
range2 = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[rowIndex + i, this.missing];
range1.Copy(range2);
}
}
catch (Exception e)
{
this.KillExcelProcess(false);
throw e;
}
}
/// <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();
}
}
