Excel的导出导入的多种方法
1. 导入
a.) 使用微软的OLEDB方式
Code
public void ExcelToDB(string path)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
DataSet ds = new DataSet();
string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"", path);
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", connection);
adapter.Fill(ds);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
b.) DCOM方式
Code
private Excel.ApplicationClass ExcelFile;
public void ExcelToDB()
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
PreExitExcel();
this.ExcelFile = new Excel.ApplicationClass();
this.ExcelFile.Visible = false;
Excel.Workbook Book;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Book = (Excel.Workbook)this.ExcelFile.Workbooks._Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet FirstSheet = (Excel.Worksheet)Book.Sheets[1];
string Name = FirstSheet.get_Range("A1", Missing.Value).Value2.ToString().Trim();
string Sex = FirstSheet.get_Range("A2", Missing.Value).Value2.ToString().Trim();
Book.Close(false, false, false);
PreExitExcel();
//省略保存数据到数据库
}
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
/// 预关闭未退出的Excel进程
/// </summary>
public void PreExitExcel()
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
System.Diagnostics.Process[] allProcess = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process thisprocess in allProcess)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string processName = thisprocess.ProcessName;
if (processName.ToLower() == "excel")
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
thisprocess.Kill();
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
catch (Exception e)
{ }
}
}
}
2. 导出
a.)用gridview导出到Excel
Code
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
/// 导出到Excel
/// </summary>
/// <param name="FileName"></param>
public bool ToExcel(string FileName)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
try
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
BindViewer();
GvFy.AllowPaging = false;
Response.Clear();
Response.Buffer = true;
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
HttpContext.Current.Response.ContentType = "application/ms-excel";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
//HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename="+ FileName + ".xls");
GvFy.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
Table2.RenderControl(hw);
Response.Output.Write(tw.ToString());
Response.Flush();
Response.End();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
catch(Exception ex)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
ShowMessageBox("报表没数据,不能够导出!!!");
return false;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SsfService.Instance.InsertLog(Dafang.Ssf.Entities.LogType.四分线损汇总, "线损分压统计表: 导出原始表" + ddlDataCycle.SelectedValue);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return true;
}
并且需要override一下VerifyRenderingInServerForm方法(这一点非常重要,否则在点击按钮后会报错),代码如下
Code
public override void VerifyRenderingInServerForm(Control control)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
// Confirms that an HtmlForm control is rendered for
}
最好在页面文件加上EnableEventValidation = "false"
Code
<%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
我们有时候需要用这中方法导出的Excel文件在导入到数据库中,就会有问题了,将会提示"外部表不是预期的格式"。出现这种错误那就是我们的Excel文件不是标准的Excel格式。我们先来看看怎么判断一个Excel文件是不是标准的格式。方法是:用记事本打开你的excel文件,看看显示是否为乱码。若是html代码,则表示你的excel文件格式不是标准的excel格式,才会提示“外部表不是预期的格式”的错误;若是乱码,我这边测试是不会提示这个错误的,可以成功导入。那么怎么才能导出标准的Excel格式的文件呢?
b.)DCOM
这种方法就解决了上面出现的问题。唯一要注意的是权限的设置。服务器端会存在c#是否有权限使用excel com组件的问题。我先贴出一个操作Excel 的类库
Code
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
/// Excel处理类
/// </summary>
public class ExcelHelper
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
public ExcelHelper()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//
// TODO: Add constructor logic here
//
}
//private Microsoft.Office.Interop.Excel. Excel;
private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public string GetAix(int x, int y)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
char[] AChars = AList.ToCharArray();
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (x >= 26)
{ return ""; }
string s = "";
s = s + AChars[x - 1].ToString();
s = s + y.ToString();
return s;
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 为指定单元格赋值
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="align">居中,居左,居右对齐</param>
/// <param name="text">值</param>
public void setValue(int x, int y, string align, string text)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.set_Value(miss, text);
if (align.ToUpper() == "CENTER")
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
if (align.ToUpper() == "LEFT")
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
if (align.ToUpper() == "RIGHT")
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
//设置单元格为最适应宽度
sheet.get_Range(this.GetAix(x, y), miss).Select();
sheet.get_Range(this.GetAix(x, y), miss).Columns.AutoFit();
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 为指定单元格赋值
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="text">值</param>
public void setValue(int x, int y, string text)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.set_Value(miss, text);
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 为指定单元格赋值
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="text"></param>
/// <param name="font"></param>
/// <param name="color"></param>
public void setValue(int x, int y, string text, System.Drawing.Font font, System.Drawing.Color color)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.setValue(x, y, text);
Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);
range.Font.Size = font.Size;
range.Font.Bold = font.Bold;
range.Font.Color = color;
range.Font.Name = font.Name;
range.Font.Italic = font.Italic;
range.Font.Underline = font.Underline;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public void insertRow(int y)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y));
range.Copy(miss);
range.Insert(Excel.XlDirection.xlDown, miss);
range.get_Range(GetAix(1, y), GetAix(25, y));
range.Select();
sheet.Paste(miss, miss);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
public void past()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string s = "a,b,c,d,e,f,g";
sheet.Paste(sheet.get_Range(this.GetAix(10, 10), miss), s);
}
public void setBorder(int x1, int y1, int x2, int y2, int Width)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
range.Borders.Weight = Width;
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 合并单元格
/// </summary>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
public void mergeCell(int x1, int y1, int x2, int y2)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// Excel.Range range =
sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).MergeCells = true;
//range.Merge(true);
// range.MergeCells
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 获取单元格区域
/// </summary>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
/// <returns></returns>
public Excel.Range getRange(int x1, int y1, int x2, int y2)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));
return range;
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 绘制边框
/// </summary>
/// <param name="x1"></param>
/// <param name="y1"></param>
/// <param name="x2"></param>
/// <param name="y2"></param>
public void DrawBorder(int x1, int y1, int x2, int y2)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders.LineStyle = 1;
sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;//设置左边线加粗
sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;//设置上边线加粗
sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;//设置右边线加粗
sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;//设置下边线加粗
//sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, null);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public Missing miss = Missing.Value; //忽略的参数OLENULL
public static Missing MissValue = Missing.Value;
private Excel.Application m_objExcel;//Excel应用程序实例
private Excel.Workbooks m_objBooks;//工作表集合
private Excel.Workbook m_objBook;//当前操作的工作表
private Excel.Worksheet sheet;//当前操作的表格
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public Excel.Worksheet CurrentSheet
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return sheet;
}
set
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.sheet = value;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public Excel.Workbooks CurrentWorkBooks
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this.m_objBooks;
}
set
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.m_objBooks = value;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public Excel.Workbook CurrentWorkBook
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return this.m_objBook;
}
set
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.m_objBook = value;
}
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 打开excel文件
/// </summary>
/// <param name="filename"></param>
public void OpenExcelFile(string filename)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
m_objExcel = new Excel.Application();
UserControl(false);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
m_objExcel.Workbooks.Open(
filename,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
m_objBook = m_objExcel.ActiveWorkbook;
sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
public void UserControl(bool usercontrol)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (m_objExcel == null)
{ return; }
m_objExcel.UserControl = usercontrol;
m_objExcel.DisplayAlerts = usercontrol;
m_objExcel.Visible = usercontrol;
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 创建excle模板
/// </summary>
public void CreateExceFile()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
m_objExcel = new Excel.Application();
UserControl(false);
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 保存excel
/// </summary>
/// <param name="FileName">文件名</param>
public void SaveAs(string FileName)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
m_objBook.SaveAs(FileName, miss, miss, miss, miss,
miss, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlLocalSessionChanges,
miss, miss, miss, miss);
m_objBook.Close(false, miss, miss);
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 回收资源
/// </summary>
public void ReleaseExcel()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (m_objExcel != null)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
GC.Collect();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 预关闭未退出的Excel进程
/// </summary>
public void PreExitExcel()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
System.Diagnostics.Process[] allProcess = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process thisprocess in allProcess)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string processName = thisprocess.ProcessName;
if (processName.ToLower() == "excel")
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
thisprocess.Kill();
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
catch (Exception e)
{ }
}
}
}
}
对类库的调用
Code
private void toExcel()
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
ExcelHelper excle = new ExcelHelper();
//处理Excel进程
excle.PreExitExcel();
//创建Excel文件
excle.CreateExceFile();
//合并A1到N1区域的单元格
excle.mergeCell(1, 1, 14, 1);
//为单元格赋值
excle.setValue(1, 1, "center", "年 月 (××单位)线损分压统计表");
excle.mergeCell(1, 2, 2, 3);
excle.setValue(1, 2,"center", "电压等级");
excle.mergeCell(3, 2, 3, 3);
excle.setValue(3, 2, "center", "输入电量(万kWh)");
excle.mergeCell(4, 2, 4, 3);
excle.setValue(4, 2, "center", "输出电量(万kWh)");
excle.mergeCell(5, 2, 8, 2);
excle.setValue(5, 2, "center", "损失电量(万kWh)");
excle.setValue(5, 3, "center", "合计");
excle.setValue(6, 3, "center", "变损");
excle.setValue(7, 3, "center", "线损");
excle.setValue(8, 3, "center", "变压器损耗所占比例(%)");
excle.mergeCell(9, 2, 9, 3);
excle.setValue(9, 2, "center", "各电压等级损耗所占比例(%)");
excle.mergeCell(10, 2, 10, 3);
excle.setValue(10, 2, "center", "线损率(%)");
excle.mergeCell(11, 2, 11, 3);
excle.setValue(11, 2, "center", "同比");
excle.mergeCell(12, 2, 12, 3);
excle.setValue(12, 2, "center", "无损电量(万kWh)");
excle.mergeCell(13, 2, 13, 3);
excle.setValue(13, 2, "center", "有损线损率(%)");
excle.mergeCell(14, 2, 14, 3);
excle.setValue(14, 2, "center", "同比");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
List<Fydydj> fydydjs = SsfService.Instance.GetFydydjs();
int i=4;
foreach (Fydydj fy in fydydjs)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
excle.mergeCell(1, i, 1, i+1);
excle.setValue(1, i,"center", fy.FydydjX);
excle.setValue(2, i,"center", "当月");
excle.setValue(2, i+1,"center", "累计");
i += 2;
}
//为工作区设置样式
excle.DrawBorder(1,1,14,i-1);
excle.SaveAs(Server.MapPath("..") + "\\Templates\\" + "线损分压统计表.xls");
excle.ReleaseExcel();
}
Dcom方式导入Excel的权限配置:1,先查看计算机管理-本地用户和组-用户(查看是否有ASPNET用户,没有则添加)
2,找到项目文件夹-属性-安全-添加-ASPNET-(添加读取和写入权限)
3,找到Microsoft.NET\Framework\v1.0.3705\CONFIG下的machine.config文件,将processModel节中的username的值设为SYSTEM(2.0以上的跳过这条)
4,在Windows的运行框中输入dcomcnfg,打开Com管理。
Microsoft Excel应用程序-属性-常规(身份验证级别:无);
标识-启动用户;
安全-(启动和缴活权限-自定义-编辑-添加(Everyone:授所有权限))
安全-(访问权限-自定义-编辑-添加(Everyone:授所有权限))
安全-(配置权限-自定义-编辑-添加(分别对ASPNET,Users,Power Users等用户:授所有权限(或者访问、运行和配置权限)))
最后重起计算机