导出带样式的EXCEL
我现在是用net自带的excel做的 搜索了很多 还是不行
问题是 导出地址要确定 而且导出只能在本机操作 在其他机器上操作 导出来的 还是在我本机
不知道有其他方式没 像那种可以自己选择导出地址 还能导出带格式的文件
有做法和思路都可以 有具体的DEMO更好 分不够的可以提 能解决问题就行
下面是我的做法.注释的比较详细了. 然后文件地址传的是绝对路径 不然会导出到c盘我的文档下面
public static string Export(DataSet ds, string file_name, string save_path)
{
if (ds != null)
{
int countR = ds.Tables[0].Rows.Count;//读取数据的行数
int countC = ds.Tables[0].Columns.Count - 1;//读取数据的列数
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass
();//实例化Excel
Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Application.Workbooks.Add(true);//创建WorkBook
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets
["Sheet1"];//创建WorkSheet
ws.Name = file_name;//重命名Sheet1
try
{
ws.Cells[1, 2] = file_name;//写表名
ws.Cells[1, 7] = "表示未付款";
ws.Cells[2, 1] = "序列";
for (int i = 0; i < countC; i++)
{
ws.Cells[2, i + 2] = ds.Tables[0].Columns[i].ColumnName;//写表头
}
for (int i = 0; i < countR; i++)
{
ws.Cells[i + 3, 1] = i + 1;
for (int j = 0; j < countC; j++)
{
ws.Cells[i + 3, j + 2] = ds.Tables[0].Rows[i].ItemArray[j];//写入数据
}
}
//利用Rang接口来控制Excel的样式
string pay = "";
Range rg = ws.get_Range(ws.Cells[1, 1], ws.Cells[countR + 2, countC + 1]);//在rg中修改所有单元格的样式
rg.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
rg.Borders.LineStyle = 1;//设置边框样式
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
pay = ds.Tables[0].Rows[i]["paytype"].ToString();
if (pay == "1")
{
rg = ws.get_Range(ws.Cells[i + 3, 7], ws.Cells[i + 3, 7]);
rg.Interior.ColorIndex = 4; //如果未付款 单元格背景设置为绿色
}
}
rg = ws.get_Range(ws.Cells[1, 2], ws.Cells[1, 5]);
rg.MergeCells = true;//合并单元格
rg.Font.Size = 20;//设置字体大小
rg.Font.Name = "黑体";//设置字体
rg.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rg.RowHeight = 40;//设置行高
//省略... 一系列格式操作
//保存文件
wb.SaveAs(save_path, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing,
System.Text.Encoding.UTF7, Type.Missing, Type.Missing);
wb.Saved = true;
excelApp.UserControl = false;
return "导出成功！";
}
catch (Exception exce)
{
return exce.Message.ToString();//捕捉并返回异常
}
finally
{
excelApp.Quit();//关闭Excel
excelApp = null;
GC.Collect();//回收资源
}
}
else
return "数据不存在！";
}
using (SaveFileDialog dlg = new SaveFileDialog())
{
dlg.Filter = "*.xls
[解决办法]
*.xls";
dlg.FileName = string.Format("{0}.xls", textBox1.Text);
if (dlg.ShowDialog() == DialogResult.OK)
{
//save excel
}
}
SSFCellStyle DefaultStyle = (HSSFCellStyle)workbook.CreateCellStyle();
DefaultStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index;
DefaultStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
DefaultStyle.BorderBottom = CellBorderType.MEDIUM;
DefaultStyle.BorderTop = CellBorderType.THIN;
using GemBox.Spreadsheet;
.......
...
{
xlWorkSheet.Cells[starRows, 7].Value = "Sub Total: HK$";
xlWorkSheet.Cells[starRows, 8].Value = list.Sum(t => t.Salary).FormatMoney(2);
xlWorkSheet.Cells[starRows, 9].Value = (list.Sum(t => t.Minute) / 60.00).FormatMoney(2);
xlWorkSheet.Cells[starRows, 7].Style.HorizontalAlignment = HorizontalAlignmentStyle.Right;
xlWorkSheet.Cells[starRows, 7].Style.Font.Weight = ExcelFont.BoldWeight;
xlWorkSheet.Cells[starRows, 8].Style.Font.Weight = ExcelFont.BoldWeight;
xlWorkSheet.Cells[starRows, 9].Style.Font.Weight = ExcelFont.BoldWeight;
}
