导出：
private void exportExcel()
{
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
Application.DoEvents();
Application.DoEvents();
this.gridControl1.ExportToXlsx(saveFileDialog1.FileName);
Application.DoEvents();
Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
appExcel.DisplayAlerts = false;
//DisplayAlerts 属性设置成 False，就不会出现这种警告。
Microsoft.Office.Interop.Excel.Workbook workbook = appExcel.Workbooks.Open(saveFileDialog1.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.Sheets sheets = workbook.Worksheets;//实例表格
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[1];//第一个表格
worksheet.Columns["A", Type.Missing].ColumnWidth = 14;
worksheet.Columns["B", Type.Missing].ColumnWidth = 5;
worksheet.Columns["C", Type.Missing].ColumnWidth = 12;
worksheet.Columns["D", Type.Missing].ColumnWidth = 10;
worksheet.Columns["E", Type.Missing].ColumnWidth = 12;
worksheet.Columns["F", Type.Missing].ColumnWidth = 16;
worksheet.Columns["G", Type.Missing].ColumnWidth = 18;
worksheet.Columns["H", Type.Missing].ColumnWidth = 7;
worksheet.Columns["I", Type.Missing].ColumnWidth = 7;
worksheet.Columns["J", Type.Missing].ColumnWidth = 7;
worksheet.Columns["K", Type.Missing].ColumnWidth = 7;
worksheet.Columns["L", Type.Missing].ColumnWidth = 10;
worksheet.Columns["M", Type.Missing].ColumnWidth = 7;
worksheet.Columns["N", Type.Missing].ColumnWidth = 7;
worksheet.Columns["O", Type.Missing].ColumnWidth = 5;
worksheet.Columns["P", Type.Missing].ColumnWidth = 5;
worksheet.Columns["Q", Type.Missing].ColumnWidth = 12;
worksheet.Columns["R", Type.Missing].ColumnWidth = 12;
worksheet.Columns["S", Type.Missing].ColumnWidth = 5;
worksheet.Columns["T", Type.Missing].ColumnWidth = 5;
worksheet.Columns["U", Type.Missing].ColumnWidth = 5;
worksheet.Columns["V", Type.Missing].ColumnWidth = 5;
worksheet.Columns["W", Type.Missing].ColumnWidth = 10;
worksheet.Columns["X", Type.Missing].ColumnWidth = 10;
worksheet.Columns["Y", Type.Missing].ColumnWidth = 10;
worksheet.Columns["Z", Type.Missing].ColumnWidth = 8;
worksheet.Columns["AA", Type.Missing].ColumnWidth = 10;
worksheet.Columns["AB", Type.Missing].ColumnWidth = 8;
worksheet.Columns["AC", Type.Missing].ColumnWidth = 10;
worksheet.Columns["AD", Type.Missing].ColumnWidth = 8;
worksheet.Columns["AE", Type.Missing].ColumnWidth = 8;
worksheet.Columns["AF", Type.Missing].ColumnWidth = 12;
worksheet.Columns["AG", Type.Missing].ColumnWidth = 15;
/// worksheet.Cells[9,"A"].value ="asd";
// Microsoft.Office.Interop.Excel.Range firstColumn = worksheet.get_Range("A1");
//Range firstColumn = (Range)xlWorkSheet.Columns[0];
// firstColumn.EntireColumn.AutoFit();
workbook.Save();
workbook.Close();
appExcel.Quit();
appExcel = null;
GC.Collect();
}
}
2014-10-1814:07:48
导入：
private void Btn_Open_Click(object sender, EventArgs e)
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.Filter = "Excel文件|*.xlsx;*.xls";
if (dlg.ShowDialog() == DialogResult.OK)
{
this.TXT_FileName.Text = dlg.FileName;
Excel.Application xlApp = new Excel.Application();
try
{
Excel.Workbook workbook = xlApp.Workbooks.Open(dlg.FileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
int n = workbook.Worksheets.Count;
CB_Sheet.Items.Clear();
for (int i = 0; i < n; i++)
{
CB_Sheet.Items.Add(((Excel.Worksheet)workbook.Worksheets[i + 1]).Name);
}
preqexcel(dlg.FileName);
}
finally
{
xlApp.Workbooks.Close();
xlApp.Quit();
xlApp = null;
GC.Collect();
this.overpricerowindex.Clear();
}
}
}
private void preqexcel(string path)
{
string strConn ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
path + ";Extended Properties="Excel 12.0;HDR=YES"";
OleDbConnection conn = new OleDbConnection(strConn);
try
{
conn.Open();
}
catch (Exception error)
{
MessageBox.Show("Microsoft.ACE.OLEDB.12.0驱动程序丢失或损坏，请重新安装ODBC驱动!", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = string.Format( "select * from [{0}$]",this.CB_Sheet.Text);
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds);
this.gridView1.Columns.Clear();
this.gridControl1.DataSource = ds.Tables[0];
conn.Close();
this.gridView1.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
this.gridView1.VertScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
foreach (DevExpress.XtraGrid.Columns.GridColumn gcol in this.gridView1.Columns)
{
gcol.MinWidth = 40;
}
}