把dategridview的datasoource写进excel, 参考 using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.IO; using Microsoft.Office.Interop.Excel; namespace TestAccess { class Program { static void Main(string[] args) {
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;"; strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;"; strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"; OleDbConnection objConnection = new OleDbConnection(strConnection); objConnection.Open(); OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection); DataSet ds = new DataSet(); myCommandd.Fill(ds, "[Sheet1$]"); System.Data.DataTable dt = ds.Tables["[Sheet1$]"]; Console.WriteLine(dt.Columns[0].ToString()); Console.WriteLine(dt.Columns[1].ToString()); DataRow drDisplay = dt.Rows[0]; int[] num = new int[dt.Columns.Count]; for (int j = 0; ; ) { for (int i = 0; i < dt.Columns.Count; i++) {
if (drDisplay[i] is DBNull) ; else num[i] += Convert.ToInt32(drDisplay[i]);
} if (++j >= dt.Rows.Count) break; drDisplay = dt.Rows[j]; } objConnection.Close(); object MissingValue = Type.Missing; Microsoft.Office.Interop.Excel.Application app = new Application(); Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue); Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; for (int i = 0; i < dt.Columns.Count; i++) { //注意下面是i+1,,excel小标默认从1开始 wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString(); }
public void WriteToExcel(DataTable table)
{
try
{
string tempImagePath = Application.StartupPath;//软件安装目录
string temp = tempImagePath + "\\Execl";//目录下的Execl文件夹
Directory.CreateDirectory(@temp);
string strFilePath = @Application.StartupPath + @"\Execl\" +名字+ ".xls"; //赋给文件的名字
System.IO.StreamWriter sw = new System.IO.StreamWriter(strFilePath,true, System.Text.Encoding.Default); //写入流
object[] values = new object[table.Columns.Count];
for (int i = 0; i < table.Columns.Count; ++i)
{
if (table.Columns[i].Caption.ToString() == "列名")
{
table.Columns[i].Caption = "自己给列起的名字";
}
sw.Write(table.Columns[i].Caption.ToString());
sw.Write('\t');
}
sw.Write("\r\n");
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < values.Length; ++j)
{
sw.Write(table.Rows[i][j].ToString());
sw.Write('\t');
}
sw.Write("\r\n");
}
sw.Flush();
sw.Close();
MessageBox.Show("成功导出[" + ds.Tables[0].Rows.Count.ToString() + "]行到Execl！");
}
catch
{
MessageBox.Show("导出Execl失败！");
}
}
一个公共导入Excel的方法
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using Microsoft.Office.Interop.Excel;namespace psySoft.UI.Win.Tool
{
/// <summary>
/// GridView导出到Excel类
/// </summary>
public static class GridViewToExcel
{
/// <summary>
/// 导出
/// </summary>
/// <param name="grid"></param>
/// <param name="fileName"></param>
/// <param name="title"></param>
public static void Print(DataGridView grid, string title, string fileName)
{
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
Microsoft.Office.Interop.Excel.Worksheet sht = null;
//导出到execl
try
{
//没有数据的话就不往下执行
if (grid.Rows.Count == 0)
return;
//实例化一个Excel.Application对象
excel = new Microsoft.Office.Interop.Excel.Application(); //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写
excel.Visible = false; //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错
wb = excel.Application.Workbooks.Add(true);
sht = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1]; sht.Name = title; for (int i = 0; i < grid.Columns.Count; i++)
{
excel.Cells[1, i + 1] = grid.Columns[i].HeaderText;
Microsoft.Office.Interop.Excel.Range rn = ((Microsoft.Office.Interop.Excel.Range)excel.Cells[1, i + 1]);
rn.Interior.Color = 0xdddddddd;
rn.HorizontalAlignment = 3;
} int iix = 0;
if (grid.AllowUserToAddRows) iix = 1; //把DataGridView当前页的数据保存在Excel中
for (int i = 0; i < grid.Rows.Count - iix; i++)
{
for (int j = 0; j < grid.Columns.Count; j++)
{
XlHAlign al=XlHAlign.xlHAlignGeneral;
switch (grid.Columns[j].DefaultCellStyle.Alignment)
{
case DataGridViewContentAlignment.BottomLeft:
case DataGridViewContentAlignment.MiddleLeft:
case DataGridViewContentAlignment.TopLeft:
al = XlHAlign.xlHAlignLeft;
break;
case DataGridViewContentAlignment.BottomCenter:
case DataGridViewContentAlignment.MiddleCenter:
case DataGridViewContentAlignment.TopCenter:
al = XlHAlign.xlHAlignCenter;
break;
case DataGridViewContentAlignment.BottomRight:
case DataGridViewContentAlignment.MiddleRight:
case DataGridViewContentAlignment.TopRight:
al = XlHAlign.xlHAlignRight;
break;
}
((Microsoft.Office.Interop.Excel.Range)excel.Cells[i+2, j + 1]).HorizontalAlignment = al;
if (grid[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + grid[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = grid[j, i].Value.ToString();
}
}
} for (int i = 0; i < grid.Columns.Count; i++)
{
((Microsoft.Office.Interop.Excel.Range)excel.Cells[Missing.Value, i + 1]).EntireColumn.AutoFit();
}
//设置禁止弹出保存和覆盖的询问提示框
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false; //保存工作簿
wb.SaveAs(fileName, XlFileFormat.xlExcel7, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
finally
{
if (sht != null)
{
ReleaseCom(sht);
sht = null;
}
if (wb != null)
{
wb.Close(false, Missing.Value, Missing.Value);
ReleaseCom(wb);
wb = null;
}
if (excel != null)
{
excel.Quit();
ReleaseCom(excel);
excel = null;
}
GC.Collect();
}
} /// <summary>
/// 释放资源
/// </summary>
/// <param name="o"></param>
private static void ReleaseCom(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);//强制释放一个对象
}
catch { }
finally
{
o = null;
}
}
}
}
参考
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace TestAccess
{
class Program
{
static void Main(string[] args)
{
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";
strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
OleDbConnection objConnection = new OleDbConnection(strConnection);
objConnection.Open();
OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);
DataSet ds = new DataSet();
myCommandd.Fill(ds, "[Sheet1$]");
System.Data.DataTable dt = ds.Tables["[Sheet1$]"];
Console.WriteLine(dt.Columns[0].ToString());
Console.WriteLine(dt.Columns[1].ToString());
DataRow drDisplay = dt.Rows[0];
int[] num = new int[dt.Columns.Count];
for (int j = 0; ; )
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (drDisplay[i] is DBNull) ;
else
num[i] += Convert.ToInt32(drDisplay[i]);
}
if (++j >= dt.Rows.Count) break;
drDisplay = dt.Rows[j];
}
objConnection.Close();
object MissingValue = Type.Missing;
Microsoft.Office.Interop.Excel.Application app = new Application();
Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue);
Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
for (int i = 0; i < dt.Columns.Count; i++)
{
//注意下面是i+1,,excel小标默认从1开始
wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString();
}
wbook.Save();
wbook.Close(true, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wsheet = null;
wbook = null;
app = null;
GC.Collect();
}
}
} 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/LCL_data/archive/2009/05/06/4154784.aspx
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.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(fileName);
workbook.Saved = true;
}
finally
{
app.UserControl = false;
app.Quit();
}
return true; }
private void SaveAs()
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true
saveFileDialog.Title = "";
saveFileDialog.ShowDialog();
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{ for (int i = 0; i < dgv.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dgv.Columns[i].HeaderText;
}
sw.WriteLine(str);
for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dgv.Columns.Count; k++)
{
if (k > 0) {
tempStr += "\t";
}
tempStr += dgv.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
} }
{
using (StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.Unicode))
{
StringBuilder rowWriter = new StringBuilder(); rowWriter.Append("姓名\t性别\t年龄\n");
foreach (DataSetView.UserRow row in dt.Rows)
{
rowWriter.Append(row.UserName).Append("\t");
rowWriter.Append(row.UserSex.ToString()).Append("\t");
rowWriter.Append(row.UserAge.ToString()).Append("\n");
} streamWriter.Write(rowWriter.ToString());
}
}这样不需要安装office excel也可以导出来.否则,像那些调用excel com的代码,你需要别人安装excel,再有office如果版本不一样,还有会出现版本不一样的问题. 以前我就碰到过这些问题.
而且很经典哦
{
using (StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.Unicode))
{
StringBuilder rowWriter = new StringBuilder(); rowWriter.Append("姓名\t性别\t年龄\n");
foreach (DataSetView.UserRow row in dt.Rows)
{
rowWriter.Append(row.UserName).Append("\t");
rowWriter.Append(row.UserSex.ToString()).Append("\t");
rowWriter.Append(row.UserAge.ToString()).Append("\n");
} streamWriter.Write(rowWriter.ToString());
}
}