控制器
1、先查sql得到数据
string webRootPath = Path.Combine(_webHostEnvironment.WebRootPath + @"\excel\");
DirectoryInfo di = new DirectoryInfo(webRootPath);//判断文件夹是否存在,不存在则创建
if (!di.Exists)
{
di.Create();
}
string time = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss");
string excelPath = @"订单表" + time + ".xls";
//表格完整路径
string filePath = Path.Combine(webRootPath + excelPath);
//读取appsetting中连接字符串//要先注入
//依赖注入Microsoft.Extensions.Configuration.IConfiguration configuration
//_configuration.GetConnectionString("DbConnection");
string connString = _configuration.GetConnectionString("DbConnection");//将sql转换为dataset
SqlServerHelp sqlServerHelper = new SqlServerHelp(connString);
var ds = sqlServerHelper.ExecuteDataSet(sql);
var result = ExcelNPOIHelper.DataTableToExcel(ds, filePath);
if (result == false)
{
return this.Ok(-1);
}return this.Ok(filePath);
工具类
public static bool DataTableToExcel(DataSet dataSet, string Outpath)
{
bool result = false;
try
{
if (dataSet == null || dataSet.Tables == null || dataSet.Tables.Count == 0 || string.IsNullOrEmpty(Outpath))
throw new Exception("输入的DataSet或路径异常");
int sheetIndex = 0;
//根据输出路径的扩展名判断workbook的实例类型
IWorkbook workbook = null;
string pathExtensionName = Outpath.Trim().Substring(Outpath.Length - 5);
if (pathExtensionName.Contains(".xlsx"))
{
workbook = new XSSFWorkbook();
}
else if (pathExtensionName.Contains(".xls"))
{
workbook = new HSSFWorkbook();
}
else
{
Outpath = Outpath.Trim() + ".xls";
workbook = new HSSFWorkbook();
}
ICellStyle style = workbook.CreateCellStyle();//创建样式
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//左对齐
style.VerticalAlignment = VerticalAlignment.Justify;//垂直居中 方法1
//style.Alignment = HorizontalAlignment.CenterSelection;//设置居中 方法2
//style.Alignment = HorizontalAlignment.Center;//设置居中 方法3
style.WrapText = true;//自动换行
//将DataSet导出为Excel
foreach (DataTable dt in dataSet.Tables)
{
sheetIndex++;
if (dt != null && dt.Rows.Count > 0)
{
ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? ("sheet" + sheetIndex) : dt.TableName);//创建一个名称为Sheet0的表
int rowCount = dt.Rows.Count;//行数
int columnCount = dt.Columns.Count;//列数//设置列头
IRow row = sheet.CreateRow(0);//excel第一行设为列头
for (int c = 0; c < columnCount; c++)
{
ICell cell = row.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName);
cell.CellStyle = style;
}
// row.Height = 25 * 20;
//设置每行每列的单元格,
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < columnCount; j++)
{
ICell cell = row.CreateCell(j);//excel第二行开始写入数据
sheet.SetColumnWidth(j, 20*256);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = style;
}
row.Height = 35 * 20;
}
//7.设置行高 Height的单位是1/20个点。例:设置高度为50个点
//row.Height = 30 * 20;
}
}
//向outPath输出数据
using (FileStream fs = File.OpenWrite(Outpath))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
fs.Flush();
fs.Close();
result = true;
}
return result;
}
catch (Exception ex)
{
return false;
}
}