asp.net将页面内容按需导入Excel，并设置excel样式，下载文件(解决打开格式与扩展名指定的格式不统一的问题)
//请求一个excel类
Microsoft.Office.Interop.Excel.ApplicationClass excel = null;
//创建 Workbook对象
Microsoft.Office.Interop.Excel._Workbook workbook = null;
Microsoft.Office.Interop.Excel.Workbooks workbooks = null;
//创建工作薄
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
try
{
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
//要保存的文件名
// string fulllFileName = fileName;
object missing = System.Reflection.Missing.Value;
try
{
workbooks = excel.Workbooks;
workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
// long totalCount = data.Count + 2;
worksheet.Columns.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;//设置水平对齐方式 为居右
range = excel.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 5]);
range.Font.Bold = "true";
range.Font.Size = "16";
range.Merge(false);//合并单元格
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
worksheet.Cells[1, 1] = "value";//设置excel的大标题
//range.Interior.ColorIndex = "15";
//range.Font.Bold = "true";
int r = 1;
int c = 0;
我是按照一行五个单元格排列的，并且根据需要凡是包括\\r\\n字符串的字符串均作为标题，设置其样式（颜色，字体大小，并合并该行的单元格），此后一格一个数据，页面内容如时间：2013年3月3日，那么excel里就存成
for (int i = 0; i < titleList.Count; i++)
{
string title = titleList[i];
if (title.Contains("\\r\\n"))
{
r = r + 2;
c = 0;
worksheet.Cells[r, c + 1] = title.Replace("\\r\\n", "");
range = excel.get_Range(worksheet.Cells[r, c + 1], worksheet.Cells[r, c + 5]);
range.Font.Bold = "true";
range.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
range.Cells.EntireColumn.AutoFit();
//设置表格边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
//range.Font.Size = "16";
range.Merge(false);//合并单元格
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(36, 40, 50));
r = r + 1;
}
else
{
if (c <= 4)
{
range = excel.get_Range(worksheet.Cells[r, c + 1], worksheet.Cells[r, c + 1]);
range.Font.Bold = "true";
//设置表格边框
worksheet.Cells[r, c + 1] = title;
}
else
{
r = r + 2;
c = 0;
worksheet.Cells[r, c + 1] = title;
}
for (int n = 0; n < 5; n++)
{
range = excel.get_Range(worksheet.Cells[r, n + 1], worksheet.Cells[r, n + 1]);
range.Cells.EntireColumn.AutoFit();
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
}
c++;
}
}
c = 0;
r = 2;
for (int i = 0; i < contentList.Count; i++)
{
string content = contentList[i];
if (content.Contains("\\r\\n"))
{
r = r + 3;
c = 0;
}
else
{
if (c <= 4)
{
range = excel.get_Range(worksheet.Cells[r, c + 1], worksheet.Cells[r, c + 1]);
//设置表格边框
// range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
worksheet.Cells[r, c + 1] = content;
}
else
{
r = r + 2;
c = 0;
worksheet.Cells[r, c + 1] = content;
}
for (int n = 0; n < 5; n++)
{
range = excel.get_Range(worksheet.Cells[r, n + 1], worksheet.Cells[r, n + 1]);
range.Cells.EntireColumn.AutoFit();
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black));
}
c++;
}
}
worksheet.Application.DisplayAlerts = false;//显示提示信息
worksheet.Name = fileName;
workbook.Saved=true;
workbook.SaveAs(Server.MapPath("/") + fileName + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);//这里解决打开文件时提示格式与扩展名指定的格式不一致的问题，这里我用的是xls，所有XlFormat.xlExcel7.
//excel.SaveWorkspace(fileName);//这里保存文件的工作空间，在asp.net中就是实现了保存对话框打开的效果，只是我现在还没有找到设置默认文件名的办法，如果有知道的，麻烦赐教一下，谢谢啦
//excel.Save(fileName);
// Response.Redirect(@"c:\下载文件.xls");
}
catch (Exception ex)
{ }
finally
{
if (workbook != null)
{
workbook.Close(false, null, null);
workbook = null;
worksheet = null;
}
}
}
catch (Exception ex)
{ }
finally
{
if (excel != null)
{
excel.Quit();
excel = null;
//DoExcel();
//下载文件
FileInfo fileInfo = new FileInfo(Server.MapPath("/") + fileName + ".xls");
if (fileInfo.Exists)
{
Response.Clear();
Response.ClearHeaders();
Response.Buffer = false;
Response.ContentType = "application/octet-stream";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileInfo.Name, System.Text.Encoding.UTF8));
Response.AppendHeader("Content-Length", fileInfo.Length.ToString());
Response.WriteFile(fileInfo.FullName);
Response.Flush();
//Response.End();
}
else
{
//文件不存在
}
}
}
