110,580
社区成员
发帖
与我相关
我的任务
分享
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
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失败!");
}
}
using (FileStream fileStream = new FileStream(filePath, FileMode.OpenOrCreate))
{
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());
}
}