C# 将dataset里面的数据导入到excel 谢谢！！！

[脸肿了]

我有一个dataset里面 里面肯能有table0,table1,talble2......
我需要把这个数据分别保存到excel里面的sheet1,sheet2,sheet3.....里面去，
需要怎么做？请高手赐教！请给出代码提示！谢谢！

[stonespace]

用ado.net，把excel作为一个数据库写入，

[stonespace]

连接字符串：
String strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+strExcelFilename+";" + "Extended Properties=Excel 8.0;";

用sql语句create table创建sheet：
"CREATE TABLE TestSheet ([ID] INTEGER,[Username] VarChar,[UserPwd] VarChar)";

然后插入就是普通的insert into语句了，

[EnForGrass]

table循环对应sheet

for(int i=0;i<dataSet.Tables.Count;i++)
{
//根据i索引导入excel
}

[昵称不能为空噢噢噢噢]

protected void AddExcel(DataSet ds, ref bool s)
{
try
{
System.Data.DataTable dt = ds.Tables[0];

// string fileName ="C:\\数据处理文件夹\\"+ Guid.NewGuid() + ".xls";
//string str = DateTime.Now.ToShortDateString();
// string[] strar=str.Split(' ');
// string str1 =strar[0]+strar[1];
SaveFileDialog sf = new SaveFileDialog();
sf.InitialDirectory = "C:\\";
sf.Filter = "excel文件（*.xls）|*.xls";

if (sf.ShowDialog()==DialogResult.OK)
{
string fileName = sf.FileName; //"C:\\数据处理文件夹\\" +str+ ".xls";
Excel.Application excel = new Excel.ApplicationClass();

int rowIndex = 1;
int colIndex = 0;

excel.Application.Workbooks.Add(true);

foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}

foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
}

excel.Visible = false;
// excel.ActiveWorkbook.s
excel.ActiveWorkbook.SaveAs(fileName, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//excel.Save(fileName);

excel.Quit();
excel = null;

GC.Collect();//垃圾回收
}
}
catch
{
s = false;
}
// }

}

[熙风]

/// <summary>
/// DataTable直接导出Excel,此方法会把DataTable的数据用Excel打开,再自己手动去保存到确切的位置
/// </summary>
/// <param name="dt">要导出Excel的DataTable</param>
/// <returns></returns>
public static void ExportExcel(System.Data.DataTable table, string savePath, bool isExit)
{
if (!isExit)//保存路径是否存在
File.Copy(System.Windows.Forms.Application.StartupPath + @"\Excel\Excel.xls", savePath);
_Excel.Application app = new _Excel.ApplicationClass();
if (app == null)
{
throw new Exception("Excel无法启动");
}
app.Visible = false;
_Excel.Workbook book = app.Workbooks.Open(savePath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
_Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;
for (int k = 1; k < ds.Tables[1].Rows.Count + 1; k++)
{
_Excel.Sheets xlSheets = book.Sheets as Sheets;
// 添加 Sheet
sheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
}
for (int j = 1; j < ds.Tables[1].Rows.Count + 1; j++)
{
_Excel.Range range = null;
sheet = (_Excel.Worksheet)book.Sheets[j];
}
}

http://blog.csdn.net/happy09li/article/details/7431967

[脸肿了]

[Quote=引用 3 楼 的回复:]
table循环对应sheet

for(int i=0;i<dataSet.Tables.Count;i++)
{
//根据i索引导入excel
}
[/Quote]

我怎么新建sheet啊？

Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[x+1]; 怎么新建？

[jy251]

综合以上的回答，给你个最终建议：

绝对不能使用for循环来单个的cell赋值，这样你的电脑CPU绝对100%占用，如果数据量大的话。使用range来赋值。

[熙风]

[Quote=引用 6 楼 的回复:]

引用 3 楼 的回复:
table循环对应sheet

for(int i=0;i<dataSet.Tables.Count;i++)
{
//根据i索引导入excel
}


我怎么新建sheet啊？

Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.……
[/Quote]

5#不是有么？

[风之影子]

public static void WriteToExcel(DataTable table, string filepath)//查询结果导出Execl
{
try
{
System.IO.StreamWriter sw = new System.IO.StreamWriter(filepath, true, System.Text.Encoding.Default);
object[] values = new object[table.Columns.Count];
for (int i = 0; i < table.Columns.Count; ++i)
{
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)
{
string xx = table.Rows[i][j].ToString();
sw.Write(xx);
sw.Write('\t');
}
sw.Write("\r\n");
}
sw.Flush();
sw.Close();
MessageBox("成功导出[" + table.Rows.Count + "]行到！" + filepath);
}
catch
{
MessageBox("导出Execl失败！");
}
}

[雨隹]

嗯 正需要这个 学习了 还得研究一下怎样导入word的表格中

[test2050]

0点了，回帖+10分哈哈

[test2050]

差两分钟啊