在C#中关于excel的导入和导出操作

一、先来看看最常见的导入操作吧!

private void Import()
{  
      //打开excel选择框
OpenFileDialog frm
= new OpenFileDialog(); frm.Filter = "Excel文件(*.xls,xlsx)|*.xls;*.xlsx"; if (frm.ShowDialog() == DialogResult.OK) { string excelName = frm.FileName; Workbook excel = new Workbook(excelName); List<string[]> importyString=GetImportExcelRoute(excel);
}
}
//循环遍历获取excel的中每行每列的值  
public List<string[]> GetImportExcelRoute(Workbook excel) { int icount = excel.Worksheets.Count; List<string[]> routList = new List<string[]>(); for (int i = 0; i < icount; i++) { Worksheet sheet = excel.Worksheets[i]; Cells cells = sheet.Cells; int rowcount = cells.MaxRow; int columncount = cells.MaxColumn; int routNameColumn = 0; int routAttachColumn = 0; int routDescColumn = 0; int routMesgColumn = 0; //获取标题所在的列
if (rowcount > 0 && columncount > 0) { //找到对应的列信息 int r0 = 2; for (int c = 0; c <= columncount; c++) { string strVal = cells[r0, c].StringValue.Trim(); if (strVal == "备注") { routDescColumn = c; break; } } r0 = 3; for (int c = 0; c <= columncount; c++) { //获取文本框内容 string strVal = cells[r0, c].StringValue.Trim(); if (strVal == "审批明细事项") { routNameColumn = c; } if (strVal == "细项") { routMesgColumn = c; } if (strVal == "前置条件及工作要求") { routAttachColumn = c; } } //找到对应标题列下面的值 if (routNameColumn > 0 && routAttachColumn > 0 && routDescColumn > 0) {//在从对应的列中找到对应的值 for (int r = 4; r <= rowcount; r++) { string[] str = new string[6]; string strRoutName = ""; string strRoutMesg = ""; string strRoutAttach = ""; string strRoutDesc = ""; string strRoutRole = ""; string strRoutPro = ""; for (int c = 0; c <= columncount; c++) { int mergcolumncount = 0; int mergrowcount = 0; bool ismerged = cells[r, c].IsMerged;//是否合并单元格 if (ismerged) { Range range = cells[r, c].GetMergedRange(); if (range != null) { mergcolumncount = range.ColumnCount; mergrowcount = range.RowCount; } } //获取文本框内容 string strVal = ""; strVal = cells[r, c].StringValue.Trim(); if (c == routNameColumn) { strRoutName = strVal; if (mergrowcount > 1 && string.IsNullOrEmpty(strRoutName)) { strRoutName = GetRoutName(routList, 0); } } if (c == routMesgColumn) { strRoutMesg = strVal; if (mergrowcount > 1 && string.IsNullOrEmpty(strRoutMesg)) { strRoutMesg = GetRoutName(routList, 1); } } if (c == routAttachColumn) { strRoutAttach = strVal; } if (c == routDescColumn) { strRoutDesc = strVal; }
}
}
}

  private string GetRoutName(List<string[]> routList, int index)
        {
            if (routList == null)
                return "";
            if (routList.Count == 0)
                return "";
            return routList[routList.Count - 1][index];
        }


可以看到导入是比较简单的,就是循环读取每行每列的值,可以看到文中有不少Cells这个属性,这个需要用到第三方的插件:using Aspose.Cells;需要在网上下载一个 Aspose.Cells的dll.

 

二、导出,就是将数据组合好后导成excel格式:

private void Export()
{
             SaveFileDialog frm = new SaveFileDialog();
                frm.Filter = "Excel文件(*.xls,xlsx)|*.xls;*.xlsx";
                frm.FileName = flowName + ".xlsx";
                if (frm.ShowDialog() == DialogResult.OK)
                {
                    string strpath = frm.FileName;
                    Workbook workbook = null;
                string strpath = _exportFlowRoutExcelPath;
                if (File.Exists(strpath))
                {
                    workbook = new Workbook(strpath);
                }
                else
                {
                    workbook = new Workbook();
                }
                   Worksheet sheet = workbook.Worksheets[0]; //工作表
                Cells cells = sheet.Cells;//单元格
string str="";//获取要导出的数据


try
{
RoutExportToExcel(workbook,cells,str
);


MessageBox.Show("导出成功!"); }
catch

{ MessageBox.Show(
"导出失败!"); } } } }

 

 public void RoutExportToExcel(Workbook workbook, Cells cells,string str)
        {
           分别得到行和列
            int routCount =0;//;
            int rowcount = 4 + routCount;
            int columnCount = 25;
            for (int i = 0; i < rowcount; i++)
            {
                Style style = SettingCellStyle(workbook, cells);
                if (i == 0)
                {
                    style.Font.Color = Color.Red;
                    style.Font.Size = 16;
                    cells.Merge(0, 0, 1, columnCount);//合并单元格
                    cells[i, 0].PutValue("综合管线决策授权体系事项");//填写内容
                    cells[0, 0].SetStyle(style);//给单元格关联样式
                    cells.SetRowHeight(0, 38);//设置行高
                    cells.SetColumnWidth(1, 20);//设置列宽
                }
                if (i > 0)
                {
                    string routeName = "";
                    string routeNote = "";
                    string routeCondition = "";
                    string guid = "";
                    if (i > 3)
                    {
                        cells.SetRowHeight(i, 42);//设置行高
                        JsonObject routJsonObj = routeJsonArray[i - 4] as JsonObject;
                        routeName = routJsonObj["routName"] == null ? "" : routJsonObj["routName"].ToString();
                        routeNote = routJsonObj["note"] == null ? "" : routJsonObj["note"].ToString();
                        routeCondition = routJsonObj["condition"] == null ? "" : routJsonObj["condition"].ToString();
                        guid = routJsonObj["guid"] == null ? "" : routJsonObj["guid"].ToString();
                    }
                    for (int j = 0; j < columnCount; j++)
                    {
                        cells[i, j].SetStyle(style);//给单元格关联样式
                        //填充行
                        if (i > 3)
                        {
                            if (j == 0)//序号
                            {
                                cells[i, j].PutValue(i - 3);//填写内容
                            }
                            else if (j == 4 || j == 5 || j == 24)//审批明细事项 细项 备注
                            {
                                FillExcelRoutProperty(i,j,style,cells,routeName,routeNote,routeCondition);
                            }
                            else if (j == 2 || j == 3 || j == 6 || j == 7 || j == 8 || j == 10 || j == 11 || j == 12 || j == 13)//类别、分类、层级或板块、地区、管线、前置条件责任部门及责任人、审核校对验收责任部门及责任人、具体审核校对验收要求、发起人
                            {
                                FillExcelRoutExtProperty(i, j, guid, style, cells,routExtPropertyJsonArray);
                            }
                            else if (j >= 14 && j <= 23)//路由角色变量(从审批人1到终审人)
                            {
                                FillExcelRoutRoleVal(i,j,guid,style,cells,routRoleValjsonArray);
                            }
                            else if (j == 9)//前置条件及工作要求
                            {
                                FillExcelRoutPreConditon(i,j,guid,style,cells,routPreConditonJsonArray);
                            }
                        }
                        else
                        {
                           SettingCellStyleAndLine(cells, i, j);//设置excel的标题行和列
                        }
                    }
                }
            }
        }

 

  /// <summary>
        /// 设置单元格样式及线条
        /// </summary>
        /// <param name="cells"></param>
        /// <param name="i"></param>
        /// <param name="j"></param>
        public void SettingCellStyleAndLine(Cells cells, int i, int j)
        {
            if (i == 1)
            {
                if (j == 0)
                {
                    cells.Merge(1, j, 3, 1);//合并单元格
                    cells[i, j].PutValue("序号");//填写内容
                    cells.SetColumnWidth(j, 5);//设置列宽
                }
                if (j == 1)
                {
                    cells.Merge(1, j, 3, 1);//合并单元格
                    cells.SetColumnWidth(j, 5);//设置列宽
                }
           }
 }
 /// <summary>
        /// 设置单元格的样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cells"></param>
        /// <returns></returns>
        public Style SettingCellStyle(Workbook workbook, Cells cells)
        {
            Style style = workbook.Styles[workbook.Styles.Add()];//新增样式
            style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
            style.Font.Name = "宋体";//文字字体
            style.Font.Size = 10;//文字大小
            style.IsLocked = false;//单元格解锁
            style.Font.IsBold = false;//粗体
            style.ForegroundColor = Color.FromArgb(255, 255, 255);//设置背景色
            style.Pattern = BackgroundType.Solid; //设置背景样式
            style.IsTextWrapped = true;//单元格内容自动换行
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
            return style;
        }

 

 

 

posted @ 2014-04-08 14:31  深山居士  阅读(...)  评论(...编辑  收藏