public void exportToExcel(System.Windows.Controls.DataGrid grid, string fileName = "Excel")
{
try
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Documents (*.xls)|*.xls";
sfd.FileName = fileName + ".xls";
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
// Copy DataGridView results to clipboard
copyAlltoClipboard(grid);
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Format column D as text before pasting results, this was required for my data
Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
rng.NumberFormat = "@";
// Paste clipboard results to worksheet range
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
// For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
// Delete blank column A and select cell A1
Microsoft.Office.Interop.Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
delRng.Delete(Type.Missing);
xlWorkSheet.get_Range("A1").Select();
// Save the excel file under the captured location from the SaveFileDialog
xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
System.Windows.Clipboard.Clear();
// roomsGrid.ClearSelection();
// Open the newly saved excel file
if (File.Exists(sfd.FileName))
{
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
catch (Exception e)
{
System.Windows.MessageBox.Show(e.ToString());
}
}
private void OutputAsExcelFile()
{
//将datagridView中的数据导出到一张表中
DataTable tempTable = this.exporeDataToTable(this.dataGridView1);
//导出信息到Excel表
Microsoft.Office.Interop.Excel.ApplicationClass myExcel;
Microsoft.Office.Interop.Excel.Workbooks myWorkBooks;
Microsoft.Office.Interop.Excel.Workbook myWorkBook;
Microsoft.Office.Interop.Excel.Worksheet myWorkSheet;
char myColumns;
Microsoft.Office.Interop.Excel.Range myRange;
object[,] myData = new object[500, 35];
int i, j;//j代表行,i代表列
myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
//显示EXCEL
myExcel.Visible = true;
if (myExcel == null)
{
MessageBox.Show("本地Excel程序无法启动!请检查您的Microsoft Office正确安装并能正常使用", "提示");
return;
}
myWorkBooks = myExcel.Workbooks;
myWorkBook = myWorkBooks.Add(System.Reflection.Missing.Value);
myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Worksheets[1];
myColumns = (char)(tempTable.Columns.Count + 64); //设置列
myRange = myWorkSheet.get_Range("A4", myColumns.ToString() + "5"); //设置列宽
int count = 0;
//设置列名
foreach (DataColumn myNewColumn in tempTable.Columns)
{
myData[0, count] = myNewColumn.ColumnName;
count = count + 1;
}
//输出datagridview中的数据记录并放在一个二维数组中
j = 1;
foreach (DataRow myRow in tempTable.Rows) //循环行
{
for (i = 0; i < tempTable.Columns.Count; i++) //循环列
{
myData[j, i] = myRow[i].ToString();
}
j++;
}
//将二维数组中的数据写到Excel中
myRange = myRange.get_Resize(tempTable.Rows.Count + 1, tempTable.Columns.Count);//创建列和行
myRange.Value2 = myData;
myRange.EntireColumn.AutoFit();
myRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
//删除前方空白三行
Microsoft.Office.Interop.Excel.Range deleteRng = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.Rows[1, System.Type.Missing];
deleteRng.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
Microsoft.Office.Interop.Excel.Range deleteRng1 = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.Rows[1, System.Type.Missing];
deleteRng1.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
Microsoft.Office.Interop.Excel.Range deleteRng2 = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.Rows[1, System.Type.Missing];
deleteRng2.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
}
static public void DeleteManager(int code)
{
int j = 0;
for (int i = 0; i < dataManagers.Rows.Count; i++)
{
if (Convert.ToInt32(dataManagers[0, i].Value) == code)
{
j = i;
dataManagers.Rows.RemoveAt(i);
break;
}
}
string filename = "E:\\Shop\\Данные\\Managers.xlsx";
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.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)ExcelWorkSheet.Rows[j + 1];
rg.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
ExcelWorkBook.Save();
ExcelApp.Quit();
}
public void TransferringDatagridviewtoExcel(DataGridView dataGridView, string FileName)
{
try
{
DialogResult dialogResult = MessageBox.Show("Kayıtları Excel'e Aktarılsın mı?", "Excel Aktarma", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (dialogResult == DialogResult.Yes)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Documents (*.xls)|*.xls";
sfd.FileName = FileName + ".xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
copyAlltoClipboard(dataGridView);
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
rng.NumberFormat = "@";
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
Microsoft.Office.Interop.Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
delRng.Delete(Type.Missing);
xlWorkSheet.get_Range("A1").Select();
xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlexcel);
Clipboard.Clear();
dataGridView.ClearSelection();
if (File.Exists(sfd.FileName))
{
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
}
catch (Exception)
{
MessageBox.Show("Excel'e Aktarılma Sırasında Hata Oluştu.");
}
}
/// <summary>
/// 在工作表中删除行
/// </summary>
/// <param name="sheet">当前工作表</param>
/// <param name="rowIndex">欲删除的行索引</param>
public void DeleteRows(Microsoft.Office.Interop.Excel.Worksheet sheet, int rowIndex)
{
try
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, Type.Missing];
range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
range = null;
}
catch (Exception e)
{ MessageBox.Show(e.Message.ToString()); }
}
private void shrani_tabelo_bt_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Documents (*.xls)|*.xls";
sfd.FileName = "tabela_ahp_metode.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
// Copy DataGridView results to clipboard
copyAlltoClipboard();
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Format column D as text before pasting results, this was required for my data
Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
rng.NumberFormat = "@";
// Paste clipboard results to worksheet range
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
Microsoft.Office.Interop.Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
delRng.Delete(Type.Missing);
xlWorkSheet.get_Range("A1").Select();
// Save the excel file under the captured location from the SaveFileDialog
xlWorkBook.SaveAs(sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
Clipboard.Clear();
dataGridView1.ClearSelection();
// Open the newly saved excel file
if (File.Exists(sfd.FileName))
{
System.Diagnostics.Process.Start(sfd.FileName);
}
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="rowIndex"></param>
/// <param name="count"></param>
public void DeleteRows(int rowIndex, int count)
{
try
{
for (int i = 0; i < count; i++)
{
range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[rowIndex, this.missing];
range.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlDown);
}
}
catch (Exception e)
{
this.KillExcelProcess(false);
throw e;
}
}
public void DeleteEmptyRows(Microsoft.Office.Interop.Excel.Worksheet sh)
{
Microsoft.Office.Interop.Excel.Range currentRow = null;
var rows = sh.Rows;
var currentIndex = sh.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell).Row;
var contentFound = false;
while (!contentFound && currentIndex > 0)
{
currentRow = rows[currentIndex];
if (excelapp.WorksheetFunction.CountA(currentRow) == 0)
{
currentRow.Delete();
}
//else contentFound = true;
Marshal.FinalReleaseComObject(currentRow);
currentIndex--;
}
}
private void button2_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml(Application.StartupPath + "\\ExcelBindingXml.xml");
Microsoft.Office.Interop.Excel.Application m_objExcel = null;
Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
Microsoft.Office.Interop.Excel.Range m_objRange = null;
object m_objOpt = System.Reflection.Missing.Value;
try
{
m_objExcel = new Microsoft.Office.Interop.Excel.Application();
m_objBook = m_objExcel.Workbooks.Open(Application.StartupPath + "\\ExcelTemplate.xlsx", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(1));
int maxRow = m_objSheet.UsedRange.Rows.Count;
int maxCol = m_objSheet.UsedRange.Columns.Count;
int TitleRow = 0;
for (int excelrow = 1; excelrow <= maxRow; excelrow++)
{
for (int col = 0; col < maxCol; col++)
{
string excelColName = ExcelColNumberToColText(col);
m_objRange = m_objSheet.get_Range(excelColName + excelrow.ToString(), m_objOpt);
m_objRange.Text.ToString().Contains("$");
TitleRow = excelrow;
break;
}
}
for (int excelrow = 1; excelrow <= ds.Tables[0].Rows.Count; excelrow++)
{
DataRow dr = ds.Tables[0].Rows[excelrow - 1];
for (int col = 0; col < maxCol; col++)
{
string excelColName = ExcelColNumberToColText(col);
m_objRange = m_objSheet.get_Range(excelColName + TitleRow.ToString(), m_objOpt);
//Microsoft.Office.Interop.Excel.Range item_objRange = m_objSheet.get_Range(excelColName + (maxRow + excelrow).ToString(), m_objOpt);
if (m_objRange.Text.ToString().Replace("$", "") == ds.Tables[0].Columns[col].ColumnName)
{
m_objSheet.Cells[maxRow + excelrow, col + 1].value = dr[col].ToString();
m_objSheet.Cells[maxRow + excelrow, col + 1].Style = m_objRange.Style;
Console.WriteLine(m_objSheet.Cells[maxRow + excelrow, col + 1].value + ":" + dr[col].ToString());
//item_objRange.Value2 = dr[col].ToString();
}
}
}
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)m_objSheet.Rows[TitleRow, m_objOpt];
range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
m_objExcel.DisplayAlerts = false;
m_objBook.SaveAs(Application.StartupPath + "\\ExcelBindingXml.xlsx", m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
m_objBook.Close(m_objOpt, m_objOpt, m_objOpt);
m_objExcel.Workbooks.Close();
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
m_objBook = null;
m_objExcel = null;
GC.Collect();
}
}
/// <summary>
/// 删除行
/// </summary>
/// <param name="sheet">worksheet</param>
/// <param name="rowIndex">行数</param>
public static void DeleteRows(this Microsoft.Office.Interop.Excel.Worksheet sheet, int rowIndex)
{
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, Type.Missing];
range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
}
private void btnPrint_Click(object sender, EventArgs e)
{
int customer_index = 0;
customer_index = dataGridView1.Columns["Customer"].Index;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
string temp = dataGridView1.Rows[i].Cells[customer_index].Value.ToString();
temp = temp.Trim();
dataGridView1.Rows[i].Cells[customer_index].Value = temp;
}
string FileName = @"C:\temp\temp.xls";
// Copy DataGridView results to clipboard
dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
dataGridView1.SelectAll();
DataObject dataObj = dataGridView1.GetClipboardContent();
if (dataObj != null)
{
Clipboard.SetDataObject(dataObj);
}
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Format column D as text before pasting results, this was required for my data
Microsoft.Office.Interop.Excel.Range rng = xlWorkSheet.get_Range("D:D").Cells;
rng.NumberFormat = "@";
// Paste clipboard results to worksheet range
Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
// For some reason column A is always blank in the worksheet. ¯\_(ツ)_/¯
// Delete blank column A and select cell A1
Microsoft.Office.Interop.Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
delRng.Delete(Type.Missing);
xlWorkSheet.get_Range("A1").Select();
Microsoft.Office.Interop.Excel.Worksheet ws = xlexcel.ActiveWorkbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range = ws.UsedRange;
//ws.Columns.ClearFormats();
//ws.Rows.ClearFormats();
//range.EntireColumn.AutoFit();
//range.EntireRow.AutoFit();
xlWorkSheet.Range["A1:H1"].Interior.Color = System.Drawing.Color.LightSkyBlue;
xlWorkSheet.Columns[2].ColumnWidth = 98.14;
xlWorkSheet.Columns[2].WrapText = true;
xlWorkSheet.Range["H1:H300"].NumberFormat = "£#,###,###.00";
ws.Columns.AutoFit();
ws.Rows.AutoFit();
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders.Color = ColorTranslator.ToOle(Color.Black);
// Save the excel file under the captured location from the SaveFileDialog
xlWorkBook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
//releaseObject(xlWorkSheet);
//releaseObject(xlWorkBook);
//releaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
Clipboard.Clear();
dataGridView1.ClearSelection();
// Open the newly saved excel file
if (File.Exists(FileName))
{
System.Diagnostics.Process.Start(FileName);
}
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks workbooks;
Microsoft.Office.Interop.Excel.Workbook excelBook;
//app = null;
//app = new Excel.Application(); // create a new instance
excelApp.DisplayAlerts = false; //turn off annoying alerts that make me want to cryyyy
uint processID = 0;
workbooks = excelApp.Workbooks;
excelBook = workbooks.Add(FileName);
Microsoft.Office.Interop.Excel.Sheets sheets = excelBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)(sheets[1]);
//Range.Rows.AutoFit();
//Range.Columns.AutoFit();
}
