private void button5_Click(object sender, EventArgs e)//导出到Excel
{
ExportForDataGridview(dataGridView1,true,"报损报溢");
}
//导出Ｅｘｃｅｌ
public static bool ExportForDataGridview(DataGridView gridView, bool isShowExcle, string mc)
{
string filePath = "f";
SaveFileDialog saf = new SaveFileDialog();
saf.FileName = mc;
saf.Filter = "Excel files(*.xls)|*.xls";
if (saf.ShowDialog() == DialogResult.OK)
{
FileInfo fileInfo = new FileInfo(saf.FileName);
filePath = fileInfo.FullName;
string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
//建立Ｅｘｃｅｌ对象
Excel.Application app = new Excel.Application();
try
{
if (app == null)
{
return false;
}
//app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return false;
}
string sLen = "";
//取得最后一列列名
char H = (char)(64 + gridView.ColumnCount / 26);
char L = (char)(64 + gridView.ColumnCount % 26);
if (gridView.ColumnCount < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
//标题
string sTmp = sLen + "1";
Range ranCaption = worksheet.get_Range(sTmp, "A1");
string[] asCaption = new string[gridView.ColumnCount];
for (int i = 0; i < gridView.ColumnCount; i++)
{
asCaption[i] = gridView.Columns[i].HeaderText;
}
ranCaption.Value2 = asCaption;
//数据
object[] obj = new object[gridView.Columns.Count];
for (int r = 0; r < gridView.RowCount - 1; r++)
{
for (int l = 0; l < gridView.Columns.Count; l++)
{
if (gridView[l, r].ValueType == typeof(DateTime))
{
obj[l] = gridView[l, r].Value.ToString();
}
else
{
obj[l] = gridView[l, r].Value;
}
}
string cell1 = sLen + ((int)(r + 2)).ToString();
string cell2 = "A" + ((int)(r + 2)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
ran.Value2 = obj;
}
//保存
workbook.SaveCopyAs(filePath);
workbook.Saved = true;
}
finally
{
//关闭
app.UserControl = false;
app.Quit();
}
}
return true;
}
到Table
public static bool ExportForDataGridview(System.Data.DataTable gridView, bool isShowExcle, string mc)
{
string filePath = "f";
SaveFileDialog saf = new SaveFileDialog();
saf.FileName = mc;
saf.Filter = "Excel files(*.xls)|*.xls";
if (saf.ShowDialog() == DialogResult.OK)
{
FileInfo fileInfo = new FileInfo(saf.FileName);
filePath = fileInfo.FullName;
string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
//建立Ｅｘｃｅｌ对象
Excel.Application app = new Excel.Application();
try
{
if (app == null)
{
return false;
}
//app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return false;
}
string sLen = "";
//取得最后一列列名
char H = (char)(64 + gridView.Columns.Count / 26);
char L = (char)(64 + gridView.Columns.Count % 26);
if (gridView.Columns.Count < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
//标题
string sTmp = sLen + "1";
Range ranCaption = worksheet.get_Range(sTmp, "A1");
string[] asCaption = new string[gridView.Columns.Count];
for (int i = 0; i < gridView.Columns.Count; i++)
{
asCaption[i] = gridView.Columns[i].ColumnName;
}
ranCaption.Value2 = asCaption;
//数据
object[] obj = new object[gridView.Columns.Count];
for (int r = 0; r < gridView.Rows.Count - 1; r++)
{
for (int l = 0; l < gridView.Columns.Count; l++)
{
if (gridView.Rows[r][l].GetType() == typeof(DateTime))
{
obj[l] = gridView.Rows[l][r].ToString();
}
else
{
obj[l] = gridView.Rows[r][l].ToString();
}
}
string cell1 = sLen + ((int)(r + 2)).ToString();
string cell2 = "A" + ((int)(r + 2)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
ran.Value2 = obj;
}
//保存
workbook.SaveCopyAs(filePath);
workbook.Saved = true;
}
finally
{
//关闭
app.UserControl = false;
app.Quit();
}
}
return true;
}
