C# 导出.xls文件的Excel
0
所有回答(6)
0
你用的什么类库，没有提供这样的功能吗，换个类库试试
0
NPOI 第三方Excel得写库
0
NPOI操作excel导出导入比较好
0
百度一下 你就知道。
0
public byte[] DataSetToExcel2007(DataSet ds, List<string> sheetName) { XSSFWorkbook workbook = new XSSFWorkbook(); bool isAutoName = false; if (sheetName == null || sheetName.Count == 0) isAutoName = true; if (ds.Tables.Count > sheetName.Count) { isAutoName = true; } if (string.IsNullOrEmpty(strExcelFileName)) strExcelFileName = "report"; for(int i=0;i<ds.Tables.Count;i++) { DataTable dt = ds.Tables[i]; string mySheetName = isAutoName ? "Sheet" + i : sheetName[i]; ISheet sheet = workbook.CreateSheet(mySheetName); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HeadercellStyle.VerticalAlignment = VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 20; foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看 //cellStyle.DataFormat = XSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.VerticalAlignment = VerticalAlignment.Center; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); DataRow.HeightInPoints = 18; foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int j = 0; j < icolIndex; j++) { sheet.AutoSizeColumn(j); } } System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms);//xssfworkbook在write后会关闭流 byte[] bytes = ms.ToArray(); return bytes; }
能写代码就不bb，你要的时这个？需要引用NPOI
0
用Free Spire.XLS (http://www.e-iceblue.cn/Downloads/Free-Spire-XLS-NET.html )代码简单，可以将数据导入到指定的sheet
using Spire.Xls;
using System.Data.OleDb;
using System.Data;
namespace test
{
class Program
{
static void Main(string[] args)
{
//链接数据库
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
OleDbCommand command = new OleDbCommand();
command.CommandText = "select * from parts";
DataSet dataSet = new System.Data.DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
dataAdapter.Fill(dataSet);
//加载excel文档到
Workbook book = new Workbook();
book.LoadFromFile(@"C:\Users\Administrator\Desktop\ToExcel.xlsx");
//获取第一个和第二个sheet
Worksheet sheet1 = book.Worksheets[0];
Worksheet sheet2 = book.Worksheets[1];
//将不同的table分别导入两个sheet
DataTable table1 = dataSet.Tables[0];
sheet1.InsertDataTable(table1, true, 1, 1);
DataTable table2 = dataSet.Tables[1];
sheet2.InsertDataTable(table2, true, 1, 1);
//保存excel文档
book.Save();
}
}
}