1、将sql数据库表中的数据导入到Excel表格里：
方法一、使用StreamWrite对象，这里要注意的是
用“ ”换列，StreamWrite对象的WriteLine方法 一行一行写入。
public static void ImportExcel()
{
DataAccess services = new DataAccess();
string sql = @"SELECT [Id]
,[Name]
,[Age]
,[Sex]
FROM [StudentManager].[dbo].[Student]";
DataTable dt = services.ExeDataTable(sql, CommandType.Text);
if (dt != null && dt.Rows.Count > 0)
{
using (StreamWriter sw = new StreamWriter(@"D:123 est.xls", false, Encoding.UTF8))
{
string head = String.Empty;
foreach (var c in dt.Columns)
{
head += c.ToString() + " ";
}
sw.WriteLine(head);
foreach (DataRow row in dt.Rows)
{
sw.WriteLine(row["Id"] + " " + row["Name"] + " " + row["Age"] + " " + row["Sex"]);
}
}
}
}
方法二、使用第三方类库：
需要引入：AppLibrary.dll和Interop.Excel.dll（Com组件）两个dll
C#读取Excel 几种方法的体会
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Collections.Generic; using AppLibrary.WriteExcel; using Excel; /// <summary> ///ExcelHelp 的摘要说明 /// </summary> public class ExcelHelp { public ExcelHelp() { // //TODO: 在此处添加构造函数逻辑 // } /**/ /// <summary> /// ASP.NET，分Sheet导出Excel文件 /// </summary> /// <param name="dv">用于导出的DataView</param> /// <param name="tmpExpDir">导出的文件夹，例如~/ExcelDownload/</param> /// <param name="refFileName">文件名，例如test.xls</param> /// <param name="sheetName">Sheet的名称，如果导出多个Sheet，会自动在名称后面加1、2、3</param> /// <param name="sheetSize">每个Sheet包含的数据行数，此数值不包括标题行。所以，对于65536行数据，请将此值设置为65535</param> /// <param name="setBorderLine">导出完成后，是否给数据加上边框线</param> public static void WebExportToExcel(DataView dv, string tmpExpDir, string refFileName, string sheetName, int sheetSize, bool setBorderLine) { //设置多少行为一个Sheet int RowsToDivideSheet = sheetSize; //计算Sheet数 int sheetCount = (dv.Table.Rows.Count - 1) / RowsToDivideSheet + 1; GC.Collect(); Application excel; _Workbook xBk; _Worksheet xSt = null; excel = new Application(); xBk = excel.Workbooks.Add(true); //申明循环中要使用的变量 int dvRowStart; int dvRowEnd; int rowIndex = 0; int colIndex = 0; //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { //初始化Sheet中的变量 rowIndex = 1; colIndex = 1; //计算起始行 dvRowStart = sheetIndex * RowsToDivideSheet; dvRowEnd = dvRowStart + RowsToDivideSheet - 1; if (dvRowEnd > dv.Table.Rows.Count - 1) { dvRowEnd = dv.Table.Rows.Count - 1; } //创建一个Sheet if (null == xSt) { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName xSt.Name = sheetName; if (sheetCount > 1) { xSt.Name += ((int)(sheetIndex + 1)).ToString(); } //取得标题 foreach (DataColumn col in dv.Table.Columns) { //设置标题格式 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true; //填值，并进行下一列 excel.Cells[rowIndex, colIndex++] = col.ColumnName; } //取得表格中数量 int drvIndex; for (drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++) { DataRowView row = dv[drvIndex]; //新起一行，当前单元格移至行首 rowIndex++; colIndex = 1; foreach (DataColumn col in dv.Table.Columns) { if (col.DataType == System.Type.GetType("System.DateTime")) { //excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } else if (col.DataType == System.Type.GetType("System.String")) { excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString(); } else { excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } colIndex++; } } //使用最佳宽度 Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]); allDataWithTitleRange.Select(); allDataWithTitleRange.Columns.AutoFit(); //xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex-1]).Columns.AutoFit(); if (setBorderLine) { allDataWithTitleRange.Borders.LineStyle = 1; } } //G:LANSUNWebAdminUserSysManager100 //excel.Visible = true; string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(tmpExpDir, refFileName)); xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); } /// <summary> /// 导出Excel /// </summary> /// <param name="dv"></param> /// <param name="SheetRows"></param> /// <param name="Columns"></param> /// <param name="ColumnName"></param> /// <param name="ExportNo"></param> public static void ToExcel(DataView dv, int SheetRows, string ExportNo, HttpResponse response) { //GC.Collect(); AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument(); doc.FileName = ExportNo + ".xls"; string SheetName = string.Empty; //记录条数 int mCount = dv.Count; //每个SHEET的数量 int inv = SheetRows; //计算当前多少个SHEET int k = Convert.ToInt32(Math.Round(Convert.ToDouble(mCount / inv))) + 1; for (int i = 0; i < k; i++) { SheetName = "数据表" + i.ToString(); AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName); AppLibrary.WriteExcel.Cells cells = sheet.Cells; //创建列样式创建列时引用 XF cellXF = doc.NewXF(); cellXF.VerticalAlignment = VerticalAlignments.Centered; cellXF.HorizontalAlignment = HorizontalAlignments.Centered; cellXF.Font.FontFamily = FontFamilies.Roman;//设置字体 默认为宋体 for (int ColIndex = 0; ColIndex < dv.Table.Columns.Count; ColIndex++) { //第一行表头 cells.Add(1, ColIndex + 1, dv.Table.Columns[ColIndex].Caption, cellXF); } //for (int ColIndex = 0; ColIndex < Columns.Count; ColIndex++) //{ // //第一行表头 // cells.Add(1, ColIndex + 1, Columns[ColIndex].ToString(), cellXF); //} int f = 1; for (int m = i * inv; m < mCount && m < (i + 1) * inv; m++) { f++; //循环主体内容 //for (int CellIndex = 0; CellIndex < ColumnName.Count; CellIndex++) //{ // cells.Add(f, CellIndex + 1, dv.Table.Rows[m][ColumnName[CellIndex].ToString()].ToString(), cellXF); //} for (int CellIndex = 0; CellIndex < dv.Table.Columns.Count; CellIndex++) { cells.Add(f, CellIndex + 1, dv.Table.Rows[m][dv.Table.Columns[CellIndex].ColumnName] is DBNull ? "" : dv.Table.Rows[m][dv.Table.Columns[CellIndex].ColumnName], cellXF); } } } //添加合计 //AppLibrary.WriteExcel.Worksheet sheetq = doc.Workbook.Worksheets.Add("合计"); //AppLibrary.WriteExcel.Cells cellsq = sheetq.Cells; //cellsq.Add(1, 1, "合计数据条数"); //cellsq.Add(1, 2, mCount); // doc.Send(); response.Flush(); response.End(); } public static void ToSeveralExcel(DataSet ds, string ExportNo, HttpResponse response, string[] sheetNames, int eachSheetCount) { //GC.Collect(); AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument(); doc.FileName = ExportNo + ".xls"; string SheetName = string.Empty; //记录条数 int mCount = ds.Tables[0].Rows.Count; for (int i = 0; i < sheetNames.Length; i++) { SheetName = sheetNames[i]; AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName); AppLibrary.WriteExcel.Cells cells = sheet.Cells; //创建列样式创建列时引用 XF cellXF = doc.NewXF(); cellXF.VerticalAlignment = VerticalAlignments.Centered; cellXF.HorizontalAlignment = HorizontalAlignments.Centered; cellXF.Font.FontFamily = FontFamilies.Roman;//设置字体 默认为宋体 for (int ColIndex = 0; ColIndex < ds.Tables[0].Columns.Count; ColIndex++) { //第一行表头 cells.Add(1, ColIndex + 1, ds.Tables[0].Columns[ColIndex].Caption, cellXF); } int f = 1; var thisSheetCount = i == 0 ? eachSheetCount : (mCount - i * eachSheetCount); for (int m = 0; m < thisSheetCount; m++) { f++; var thisrowCount = (i * eachSheetCount) + m; for (int CellIndex = 0; CellIndex < ds.Tables[0].Columns.Count; CellIndex++) { cells.Add(f, CellIndex + 1, ds.Tables[0].Rows[thisrowCount][ds.Tables[0].Columns[CellIndex].ColumnName].ToString(), cellXF); } } } doc.Send(); response.Flush(); response.End(); } #region 读写Excel /// <summary> /// 读取Excel数据 /// </summary> /// <param name="FileName"></param> public static void Read(string FileName) { AppLibrary.ReadExcel.Workbook workbook = null; workbook = AppLibrary.ReadExcel.Workbook.getWorkbook(FileName); int k = 0; System.Text.StringBuilder sb = new System.Text.StringBuilder(); foreach (AppLibrary.ReadExcel.Sheet sheet in workbook.Sheets) { k++; sb.Append("当前为第" + k + "个Sheet<br/><br/>"); sb.Append("<table class='scroll' cellpadding=0 cellspacing=0>"); for (int iRow = 0; iRow < sheet.Rows; iRow++) { sb.Append("<tr>"); sb.Append("<td>"); sb.Append("当前为第" + (iRow + 1) + "行"); sb.Append("</td>"); for (int iCol = 0; iCol < sheet.Columns; iCol++) { AppLibrary.ReadExcel.Cell cell = sheet.getCell(iCol, iRow); object val = cell.Value; string cellValue = (val != null) ? val.ToString() : ""; sb.Append("<td>"); sb.Append(cellValue); sb.Append("</td>"); } sb.Append("</tr>"); } sb.Append("</table><br/><br/>"); } HttpContext.Current.Response.Write(sb.ToString()); } /// <summary> /// /// </summary> /// <param name="ds">DataSet对象</param> /// <param name="ExportNo">保存的Excel文件名</param> /// <param name="response">当前的HttpResponse对象</param> /// <param name="sheetNames">Excel表格名 和 DadaSet对象中的表个数一一对应</param> public static void ToExcel(DataSet ds, string ExportNo, HttpResponseBase response, string[] sheetNames) { //GC.Collect(); AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument(); doc.FileName = ExportNo + ".xls"; string SheetName = string.Empty; int mCount = 0; for (int i = 0; i < sheetNames.Length; i++) { SheetName = sheetNames[i]; //记录条数 mCount = ds.Tables[0].Rows.Count; AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName); AppLibrary.WriteExcel.Cells cells = sheet.Cells; //创建列样式创建列时引用 XF cellXF = doc.NewXF(); cellXF.VerticalAlignment = VerticalAlignments.Centered; cellXF.HorizontalAlignment = HorizontalAlignments.Centered; cellXF.Font.FontFamily = FontFamilies.Roman;//设置字体 默认为宋体 for (int ColIndex = 0; ColIndex < ds.Tables[i].Columns.Count; ColIndex++) { //第一行表头 cells.Add(1, ColIndex + 1, ds.Tables[i].Columns[ColIndex].Caption, cellXF); } int f = 1; for (int m = 0; m < mCount; m++) { f++; for (int CellIndex = 0; CellIndex < ds.Tables[i].Columns.Count; CellIndex++) { cells.Add(f, CellIndex + 1, ds.Tables[i].Rows[m][ds.Tables[i].Columns[CellIndex].ColumnName].ToString(), cellXF); } } } doc.Send(); response.Flush(); response.End(); } #endregion }