Asp.net MVC、Extjs（运用Linq to SQL和List泛型）批量更新、删除、打印（使用CKEditor）、导出Excel

2010-09-07 02:29 夜雨瞳阅读(8495) 评论(20) 编辑收藏举报

1、批量操作思想的大概模型：

更新：

删除：

2、导出Excel的特点

按页面渲染来导出数据

3、打印（运用的是CKEditor）

4、一些程序解释说明

4.1@page指令 validateRequest的作用

在 Web应用程序中，要阻止依赖于恶意输入字符串的黑客攻击，约束和验证用户输入是必不可少的。跨站点脚本攻击就是此类攻击的一个示例。其他类型的恶意数据或不需要的数据可以通过各种形式的输入在请求中传入。通过在应用程序中的较低级别限制允许通过的数据类型，就可以阻止不希望的事件，即使当使用代码的程序员没有恰当地使用合适的验证技术时也是如此。

当请求验证检测到潜在的恶意客户端输入时，会引发此异常来中止请求处理。请求中止能够指示危害您的应用程序安全的企图，例如跨站点脚本攻击。强烈建议让您的应用程序显式地检查与请求中止有关的所有输入。不过，您也可以通过将 @ Page 指令中的 validateRequest 属性设置为 false 来禁用请求验证，如下面的示例所示：

<%@ Page validateRequest="false" %>

要禁用应用程序的请求验证，必须修改应用程序的 Web.config 文件或创建一个 Web.config 文件，并将 pages 节的 validateRequest 属性设置为 false，如下面的示例所示：

<configuration>

<system.web>

</system.web>

</configuration>

[AcceptVerbs(HttpVerbs.Post)]
[ValidateInput(false)]//将它导出excel的验证设置为false
public ActionResult ExportGrid()
{
    if (Request["ExportContent"] != "")
    {
 
        string tmpFileName = "export.xls";
        string tmpContent = Request["ExportContent"];//获取传递上来的文件内容  
        if (Request["ExportFile"] != "")
        {
            tmpFileName = Request["ExportFile"];//获取传递上来的文件名  
            tmpFileName = System.Web.HttpUtility.UrlEncode(Request.ContentEncoding.GetBytes(tmpFileName));//处理中文文件名的情况   
        }
 
        Response.Write("&lt;script&gt;document.close();&lt;/script&gt;");
        Response.Clear();
        Response.Buffer = true;
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", "attachment;filename=\"" + tmpFileName + "\"");
 
        Response.Charset = "";
 
        System.IO.StringWriter tmpSW = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter tmpHTW = new System.Web.UI.HtmlTextWriter(tmpSW);
        tmpHTW.WriteLine(tmpContent);
        Response.Write(tmpSW.ToString());
        Response.End();
    }
 
    return View();
}

4.2 将前台的数据转换为Json字符串传送给后台进行List泛型反序列化，需要一些凑合的函数或者方法

Ext.namespace("StrToJson");
StrToJson.ToJsonUpdateRecord = function(items) {
    var jsonData = "[";
    for (i = 0; i < items.length; i++) {
        record = items[i];
        if (record.dirty) {
            jsonData += Ext.util.JSON.encode(record.data) + ",";
        }
    }
    jsonData = jsonData.substring(0, jsonData.length - 1) + "]";
    return jsonData;
}

4.3 对ExtJs官方提供的ExportToGrid.js的文件，可以自己进行部分的改动。

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

var Base64 = (function() {
    // Private property
    var keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";
 
    // Private method for UTF-8 encoding
    function utf8Encode(string) {
        string = string.replace(/\r\n/g,"\n");
        var utftext = "";
        for (var n = 0; n < string.length; n++) {
            var c = string.charCodeAt(n);
            if (c < 128) {
                utftext += String.fromCharCode(c);
            }
            else if((c > 127) && (c < 2048)) {
                utftext += String.fromCharCode((c >> 6) | 192);
                utftext += String.fromCharCode((c & 63) | 128);
            }
            else {
                utftext += String.fromCharCode((c >> 12) | 224);
                utftext += String.fromCharCode(((c >> 6) & 63) | 128);
                utftext += String.fromCharCode((c & 63) | 128);
            }
        }
        return utftext;
    }
 
    // Public method for encoding
    return {
        encode : (typeof btoa == 'function') ? function(input) {
            return btoa(utf8Encode(input));
        } : function (input) {
            var output = "";
            var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
            var i = 0;
            input = utf8Encode(input);
            while (i < input.length) {
                chr1 = input.charCodeAt(i++);
                chr2 = input.charCodeAt(i++);
                chr3 = input.charCodeAt(i++);
                enc1 = chr1 >> 2;
                enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
                enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
                enc4 = chr3 & 63;
                if (isNaN(chr2)) {
                    enc3 = enc4 = 64;
                } else if (isNaN(chr3)) {
                    enc4 = 64;
                }
                output = output +
                keyStr.charAt(enc1) + keyStr.charAt(enc2) +
                keyStr.charAt(enc3) + keyStr.charAt(enc4);
            }
            return output;
        }
    };
})();
 
Ext.override(Ext.grid.GridPanel, {
    getExcelXml: function(includeHidden) {
        var worksheet = this.createWorksheet(includeHidden);
        var totalWidth = this.getColumnModel().getTotalWidth(includeHidden);
        return '<xml version="1.0" encoding="utf-8">' +
            '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">' +
            '<o:DocumentProperties><o:Title>' + this.title + '</o:Title></o:DocumentProperties>' +
            '<ss:ExcelWorkbook>' +
            '<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' +
            '<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' +
            '<ss:ProtectStructure>False</ss:ProtectStructure>' +
            '<ss:ProtectWindows>False</ss:ProtectWindows>' +
            '</ss:ExcelWorkbook>' +
            '<ss:Styles>' +
            '<ss:Style ss:ID="Default">' +
            '<ss:Alignment ss:Vertical="Top" ss:WrapText="1" />' +
            '<ss:Font ss:FontName="arial" ss:Size="10" />' +
            '<ss:Borders>' +
            //边框颜色
            '<ss:Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' +
            '<ss:Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' +
            '<ss:Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' +
            '<ss:Border ss:Color="#000000" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' +
            '</ss:Borders>' +
            '<ss:Interior />' +
            '<ss:NumberFormat />' +
            '<ss:Protection />' +
            '</ss:Style>' +
            '<ss:Style ss:ID="title">' +
            '<ss:Borders />' +
            '<ss:Font />' +
            '<ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" />' +
            '<ss:NumberFormat ss:Format="@" />' +
            '</ss:Style>' +
            '<ss:Style ss:ID="headercell">' +
            '<ss:Font ss:Bold="1" ss:Size="10" />' +
            '<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" />' +
            '<ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" />' +
            '</ss:Style>' +
            '<ss:Style ss:ID="even">' +
            //这里是表格中奇数行的背景色,默认为#FFFFFF白色
            '<ss:Interior ss:Pattern="Solid" ss:Color="#FFFFFF" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="even" ss:ID="evendate">' +
            '<ss:NumberFormat ss:Format="yyyy-mm-dd" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="even" ss:ID="evenint">' +
            '<ss:NumberFormat ss:Format="0" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="even" ss:ID="evenfloat">' +
            '<ss:NumberFormat ss:Format="0.00" />' +
            '</ss:Style>' +
            '<ss:Style ss:ID="odd">' +
            //这里是表格中偶数行的背景色,默认为#FFFFFF白色
            '<ss:Interior ss:Pattern="Solid" ss:Color="#FFFFFF" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="odd" ss:ID="odddate">' +
            '<ss:NumberFormat ss:Format="yyyy-mm-dd" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="odd" ss:ID="oddint">' +
            '<ss:NumberFormat ss:Format="0" />' +
            '</ss:Style>' +
            '<ss:Style ss:Parent="odd" ss:ID="oddfloat">' +
            '<ss:NumberFormat ss:Format="0.00" />' +
            '</ss:Style>' +
            '</ss:Styles>' +
            worksheet.xml +
            '</ss:Workbook>';
    },
 
    createWorksheet: function(includeHidden) {
        // Calculate cell data types and extra class names which affect formatting
        var cellType = [];
        var cellTypeClass = [];
        var cm = this.getColumnModel();
        var totalWidthInPixels = 0;
        var colXml = '';
        var headerXml = '';
        var visibleColumnCountReduction = 0;
        var colCount = cm.getColumnCount();
        for (var i = 0; i < colCount; i++) {
            if ((cm.getDataIndex(i) != '')
                && (includeHidden || !cm.isHidden(i))) {
                var w = cm.getColumnWidth(i)
                totalWidthInPixels += w;
                //这里做一些判断，将一些网络参数如‘操作’过滤
                if (cm.getColumnHeader(i) === "" || cm.getDataIndex(i)==="operate") {
                    cellType.push("None");
                    cellTypeClass.push("");
                    ++visibleColumnCountReduction;
                }
                else
                {
                    colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="' + w + '" />';
                    headerXml += '<ss:Cell ss:StyleID="headercell">' +
                        '<ss:Data ss:Type="String">' + cm.getColumnHeader(i) + '</ss:Data>' +
                        '<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';
                    var fld = this.store.recordType.prototype.fields.get(cm.getDataIndex(i));
                    switch(fld.type) {
                        case "int":
                            cellType.push("Number");
                            cellTypeClass.push("int");
                            break;
                        case "float":
                            cellType.push("Number");
                            cellTypeClass.push("float");
                            break;
                        case "bool":
                        case "boolean":
                            cellType.push("String");
                            cellTypeClass.push("");
                            break;
                        case "date":
                            cellType.push("DateTime");
                            cellTypeClass.push("date");
                            break;
                        default:
                            cellType.push("String");
                            cellTypeClass.push("");
                            break;
                    }
                }
            }
        }
        var visibleColumnCount = cellType.length - visibleColumnCountReduction;
 
        var result = {
            height: 9000,
            width: Math.floor(totalWidthInPixels * 30) + 50
        };
 
        // Generate worksheet header details.
        var t = '<ss:Worksheet ss:Name="' + this.title + '">' +
            '<ss:Names>' +
            '<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + this.title + '\'!R1:R2" />' +
            '</ss:Names>' +
            '<ss:Table x:FullRows="1" x:FullColumns="1"' +
            ' ss:ExpandedColumnCount="' + (visibleColumnCount + 2) +
            '" ss:ExpandedRowCount="' + (this.store.getCount() + 2) + '">' +
            colXml +
//            '<ss:Row ss:Height="38">' +
//            '<ss:Cell ss:StyleID="title" ss:MergeAcross="' + (visibleColumnCount - 1) + '">' +
//            '<ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String">' +
//            '<html:B>'+
//            //表头标题
//            //'Generated by ExtJS'
//            '</html:B></ss:Data><ss:NamedCell ss:Name="Print_Titles" />' +
//            '</ss:Cell>' +
//            '</ss:Row>' +
            '<ss:Row ss:AutoFitHeight="1">' +
            headerXml +
            '</ss:Row>';
 
        // Generate the data rows from the data in the Store
        for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {
            t += '<ss:Row>';
            var cellClass = (i & 1) ? 'odd' : 'even';
            r = it[i].data;
            var k = 0;
            for (var j = 0; j < colCount; j++) {
                if ((cm.getDataIndex(j) != '')
                    && (includeHidden || !cm.isHidden(j))) {
 
                    var v = r[cm.getDataIndex(j)];
                    if (typeof cm.config[j].renderer == 'function' && cellType[k] != 'DateTime') {
                        var m = {};
                        v = cm.config[j].renderer(v, m, it[i], i, j, this.store);
                        var re = /<[^>]+>/g;
                        if (v) {
                            v = v.toString().replace(re, '');
                        }
                        else {
                            v = '';
                        }
                    }
 
                    if (cellType[k] !== "None") {
                        t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">';
                        if (cellType[k] == 'DateTime') {
                            t += v.format('Y-m-d');
                        } else {
                            t += v;
                        }
                        t +='</ss:Data></ss:Cell>';
                    }
                    k++;
                }
            }
            t += '</ss:Row>';
        }
 
        result.xml = t + '</ss:Table>' +
            '<x:WorksheetOptions>' +
            '<x:PageSetup>' +
            '<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' +
            '<x:Footer x:Data="Page &P of &N" x:Margin="0.5" />' +
            '<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' +
            '</x:PageSetup>' +
            '<x:FitToPage />' +
            '<x:Print>' +
            '<x:PrintErrors>Blank</x:PrintErrors>' +
            '<x:FitWidth>1</x:FitWidth>' +
            '<x:FitHeight>32767</x:FitHeight>' +
            '<x:ValidPrinterInfo />' +
            '<x:VerticalResolution>600</x:VerticalResolution>' +
            '</x:Print>' +
            '<x:Selected />' +
            '<x:DoNotDisplayGridlines />' +
            '<x:ProtectObjects>False</x:ProtectObjects>' +
            '<x:ProtectScenarios>False</x:ProtectScenarios>' +
            '</x:WorksheetOptions>' +
            '</ss:Worksheet>';
        return result;
    }
});

5、总结

这个EditorGridPanelr做的缺点很多。

5.1、在EditorGridPanel的批量更新上，用了SQL的update语句，因为Linq的批量更新上，转化后的SQL语句在效率上还是有些缺憾的。当然，这样子写程序有违背了程序之美。其中，批量更新网上有不少的例子，如：http://www.aneyfamily.com/terryandann/post/2008/04/batch-updates-and-deletes-with-linq-to-sql.aspx，但是因为能力有限，所以只能用自己的方法解决一些像这样子的问题。

5.2、在导出Excel时，暂时我只做了导出当前的页面，没有考虑导出全部的页面。而且，也试过了几个浏览器，如firefox、IE等，但是不完全兼容。firefox可以导出，IE7、IE8也可以。

5.3、打印工作做得不大好，可以打印选中的记录，却打印不了“根据页面渲染后”（如减少列，按姓名、编号==排序）。在很多方面上做得还是不够完整。

5.4、直接用SQL的Update语句，要是断电的话，那怎么办？更新数据更新到一半？所以在异常捕获方面也做得不大好，很乐意大家提出建议。