看你是需要操作Excel,那么使用LinqToExcel是可以的,或者使用第三方库,比如NPOI之类的。
我刚才找了下,很久以前写了个方法,是winform的,你看看有帮助没。
#region 导出为excel-npoi
/// <summary>
/// 弹窗选择保存路径-excel类型
/// </summary>
/// <param name="fileName">文件名</param>
/// <returns>为空或者路径</returns>
public static string GetFileNameSaveDialog(string fileName)
{
if (fileName == "")
return "";
//保存的文件名
string saveFileName = "";
try
{
SaveFileDialog saveDialog = new SaveFileDialog();
//默认文件名
saveDialog.FileName = fileName;
//默认文件类型
saveDialog.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
//默认文件类型显示顺序
saveDialog.FilterIndex = 2;
//点了取消
if (saveDialog.ShowDialog() == DialogResult.Cancel)
return "";
saveFileName = saveDialog.FileName;
//获取文件名,不带路径
string fileNameExt = saveFileName.Substring(saveFileName.LastIndexOf("\\") + 1);
//获取文件路径,不带文件名
//FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));
//获取后缀名
string ext = Path.GetExtension(saveFileName).ToLower();
//获取文件名,不带路径与后缀
string nameExt = fileNameExt.Replace(ext, "");
if (nameExt == "")
return "";
}
catch
{
saveFileName = "";
}
return saveFileName;
}
/// <summary>
/// DataTable导出excel-npoi
/// </summary>
/// <param name="dt">数据</param>
/// <param name="fileName">文件名</param>
/// <returns>0成功,1文件名为空,2点了取消,其它为catch</returns>
public static string DataToExcel(DataTable dt, string fileName)
{
try
{
if (dt == null || dt.Rows.Count == 0)
return "数据为空";
//文件后缀名
string ext = Path.GetExtension(fileName).ToLower();
IWorkbook workbook;
if (ext == ".xls")
workbook = new HSSFWorkbook();
else if (ext == ".xlsx")
workbook = new XSSFWorkbook();
else
return "文件后缀名错误";
//ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
ISheet sheet = workbook.CreateSheet("Sheet1");
#region 单元格及字体样式
//标题样式
ICellStyle styleHeader = workbook.CreateCellStyle();//创建样式对象
IFont fontHeader = workbook.CreateFont(); //创建一个字体样式对象
fontHeader.FontName = "黑体"; //和excel里面的字体对应
//fontHeader.IsItalic = true; //斜体
fontHeader.FontHeightInPoints = 12;//字体大小
//fontHeader.Boldweight = short.MaxValue;//字体加粗
styleHeader.SetFont(fontHeader); //将字体样式赋给样式对象
styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;//单元格背景色
styleHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//与背景色一同设置边框
styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中
//styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
styleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//内容样式
ICellStyle styleContent = workbook.CreateCellStyle();//创建样式对象
IFont fontContent = workbook.CreateFont(); //创建一个字体样式对象
fontContent.FontName = "宋体"; //和excel里面的字体对应
fontContent.FontHeightInPoints = 11;//字体大小
//fontContent.IsItalic = true; //斜体
//fontContent.Boldweight = short.MaxValue;//字体加粗
styleContent.SetFont(fontContent); //将字体样式赋给样式对象
//styleContent.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;//单元格背景色
//styleContent.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//与背景色一同设置边框
styleContent.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中
//styleContent.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
styleContent.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//边框
styleContent.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleContent.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleContent.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//row.Height = 30 * 20;//设置单元格的高度
//sheet.SetColumnWidth(0, 30 * 256);//设置单元格的宽度
#endregion
IRow row;
//数据
for (int i = 0; i <= dt.Rows.Count; i++)
{
row = sheet.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
if (i == 0)
{
cell.SetCellValue(dt.Columns[j].ColumnName);
cell.SetCellType(CellType.String);
cell.CellStyle = styleHeader;
}
else
{
cell.SetCellValue(dt.Rows[i - 1][j].ToString());
cell.SetCellType(CellType.String);
cell.CellStyle = styleContent;
}
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
stream.Close();
stream.Dispose();
return "0";
}
catch (Exception ex)
{
return ex.Message;
}
}
/// <summary>
/// DataTable导出excel-npoi
/// </summary>
/// <param name="dt">数据</param>
/// <param name="gvw">控件-取列</param>
/// <param name="fileName">文件名</param>
/// <returns>0成功,1文件名为空,2点了取消,其它为catch</returns>
public static string DataToExcel(DataTable dt, DataGridView gvw, string fileName)
{
try
{
if (dt == null || dt.Rows.Count == 0)
return "数据为空";
//获取后缀名
string ext = Path.GetExtension(fileName).ToLower();
IWorkbook workbook;
if (ext == ".xls")
workbook = new HSSFWorkbook();
else if (ext == ".xlsx")
workbook = new XSSFWorkbook();
else
return "文件后缀名有误";
//ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
ISheet sheet = workbook.CreateSheet("Sheet1");
#region 单元格及字体样式
//标题样式
ICellStyle styleHeader = workbook.CreateCellStyle();//创建样式对象
IFont fontHeader = workbook.CreateFont(); //创建一个字体样式对象
fontHeader.FontName = "黑体"; //和excel里面的字体对应
//fontHeader.IsItalic = true; //斜体
fontHeader.FontHeightInPoints = 12;//字体大小
//fontHeader.Boldweight = short.MaxValue;//字体加粗
styleHeader.SetFont(fontHeader); //将字体样式赋给样式对象
styleHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;//单元格背景色
styleHeader.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//与背景色一同设置边框
styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中
//styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
styleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//内容样式
ICellStyle styleContent = workbook.CreateCellStyle();//创建样式对象
IFont fontContent = workbook.CreateFont(); //创建一个字体样式对象
fontContent.FontName = "宋体"; //和excel里面的字体对应
fontContent.FontHeightInPoints = 11;//字体大小
//fontContent.IsItalic = true; //斜体
//fontContent.Boldweight = short.MaxValue;//字体加粗
styleContent.SetFont(fontContent); //将字体样式赋给样式对象
//styleContent.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;//单元格背景色
//styleContent.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//与背景色一同设置边框
styleContent.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中
//styleContent.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
styleContent.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//边框
styleContent.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleContent.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleContent.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//row.Height = 30 * 20;//设置单元格的高度
//sheet.SetColumnWidth(0, 30 * 256);//设置单元格的宽度
#endregion
IRow row;
ICell cell;
int cellIndex = 0;//单元格位置
for (int i = 0; i <= dt.Rows.Count; i++)
{
row = sheet.CreateRow(i);
for (int j = 0; j < gvw.ColumnCount; j++)
{
if (j == 0)
cellIndex = 0;
///gvw列有效
if (gvw.Columns[j].Visible && gvw.Columns[j].DataPropertyName != "")
{
sheet.SetColumnWidth(cellIndex, 15 * 256);//设置第cellIndex列的宽度为15
if (i == 0)
{
//表头
cell = row.CreateCell(cellIndex);
cell.SetCellValue(gvw.Columns[j].HeaderText);
cell.SetCellType(CellType.String);
cell.CellStyle = styleHeader;
}
else
{
//数据
cell = row.CreateCell(cellIndex);
cell.SetCellValue(dt.Rows[i - 1][gvw.Columns[j].DataPropertyName].ToString());
cell.SetCellType(CellType.String);
cell.CellStyle = styleContent;
}
cellIndex++;
}
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
stream.Close();
stream.Dispose();
return "0";
}
catch (Exception ex)
{
return ex.Message;
}
}
#endregion