62,046
社区成员
发帖
与我相关
我的任务
分享
DataSet ds = db.GetDataSet(query);
if (ds != null)
{
string _template = Server.MapPath("/ExcelTemplate/reg.xls");
System.Random ran = new Random();
string _strFileName = string.Format("{0}_{1}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"), ran.Next(9999));
Galsun.Common.NOPIHelper.ExportByWeb(ds.Tables[0], _template, 3, _strFileName);
}
private void button1_Click(object sender, EventArgs e)
{
ExportToExcel(@"d:/abcd.xls");
}
public static void ExportToExcel(string FileSavePath)
{
Excel.Application excel = null;
Excel._Workbook xBk = null;
Excel._Worksheet xSt = null;
try
{
excel = new Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
//创建一个Sheet
if (null == xSt)
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
string str = "需要显示的身份证号码";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, 1]).NumberFormatLocal = "@";
xSt.Cells[ 1, 1] = str;
xSt.Columns.AutoFit();
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true;//注意一定要加上这句
object objOpt = System.Reflection.Missing.Value;
excel.Visible = false;
xBk.SaveCopyAs(FileSavePath);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
}
catch (Exception e)
{
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
}
}
string str = "需要显示的身份证号码";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[1, 1]).NumberFormatLocal = "@";//设置单元格格式为文本
xSt.Cells[ 1, 1] = str;
xSt.Columns.AutoFit();
157 #region 填充内容
158 HSSFRow dataRow = sheet.CreateRow(rowIndex);
159 foreach (DataColumn column in dtSource.Columns)
160 {
161 HSSFCell newCell = dataRow.CreateCell(column.Ordinal);
162
163 string drValue = row[column].ToString();
164
165 switch (column.DataType.ToString())
166 {
167 case "System.String"://字符串类型
168 newCell.SetCellValue(drValue);
169 break;
170 case "System.DateTime"://日期类型
171 DateTime dateV;
172 DateTime.TryParse(drValue, out dateV);
173 newCell.SetCellValue(dateV);
174
175 newCell.CellStyle = dateStyle;//格式化显示
176 break;
177 case "System.Boolean"://布尔型
178 bool boolV = false;
179 bool.TryParse(drValue, out boolV);
180 newCell.SetCellValue(boolV);
181 break;
182 case "System.Int16"://整型
183 case "System.Int32":
184 case "System.Int64":
185 case "System.Byte":
186 int intV = 0;
187 int.TryParse(drValue, out intV);
188 newCell.SetCellValue(intV);
189 break;
190 case "System.Decimal"://浮点型
191 case "System.Double":
192 double doubV = 0;
193 double.TryParse(drValue, out doubV);
194 newCell.SetCellValue(doubV);
195 break;
196 case "System.DBNull"://空值处理
197 newCell.SetCellValue("");
198 break;
199 default:
200 newCell.SetCellValue("");
201 break;
202 }
203
204 }
205 #endregion