public void SetRowHeight(PointInfo startPoint, PointInfo endPoint, double height)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
if (height != 0)
{
objRange.RowHeight = height;
}
}
/// <summary>
/// Insert picture to excel sheet
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="imagePath"></param>
public void InsertPicutre(PointInfo startPoint, PointInfo endPoint, Image image)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
string imagePath = "";
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
imagePath = @"C:\" + Guid.NewGuid().ToString() + ".jpg";
((Bitmap)image).Save(imagePath);
((Excel.Pictures)CurSheet.Pictures(mValue)).Insert(imagePath, mValue);
pictureIndex++;
((Excel.Picture)CurSheet.Pictures(pictureIndex)).Left = (double)objRange.Left;
((Excel.Picture)CurSheet.Pictures(pictureIndex)).Top = (double)objRange.Top;
double height = ((Excel.Picture)CurSheet.Pictures(pictureIndex)).Height;
if ((double)objRange.RowHeight < height)
{
objRange.RowHeight = height;
}
File.Delete(imagePath);
}
/// <summary>
/// Set excel row height
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="font">letter font</param>
/// <param name="height">unit by letter count</param>
public void SetRowHeight(PointInfo startPoint, PointInfo endPoint, System.Drawing.Font font, int height)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
if (height != 0)
{
objRange.RowHeight = UnitConversion.GetLetterHeight(height, font);
}
}
/// <summary>
/// Write value to worksheet
/// </summary>
/// <param name="startPoint"></param>
/// <param name="endPoint"></param>
/// <param name="objValue">ReportItem or FieldItem or other value</param>
public void WriteValue(PointInfo startPoint, PointInfo endPoint, object objValue)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
objRange.Value2 = objValue;
}
public void WriteFormatedValue(PointInfo startPoint, PointInfo endPoint, ReportItem reportItem)
{
#region Variable Definition
object[] cell = null;
object startCell = null;
object endCell = null;
#endregion
cell = this.ConvertPointToCell(startPoint, endPoint);
startCell = cell[0];
endCell = cell[1];
objRange = CurSheet.get_Range(startCell, endCell);
if (startCell.ToString() != endCell.ToString())
{
objRange.Merge(mValue);
}
switch (reportItem.ContentAlignment)
{
case HorizontalAlignment.Center:
objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
break;
case HorizontalAlignment.Left:
objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
break;
case HorizontalAlignment.Right:
objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
break;
default:
objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
break;
}
if (reportItem.GetType().Name == "ReportConstantItem")
{
switch (((ReportConstantItem)reportItem).Style)
{
case ReportConstantItem.StyleType.PageIndex:
case ReportConstantItem.StyleType.PageIndexAndTotalPageCount:
if (reportItem.Format == String.Empty)
{
objRange.Value2 = String.Format(reportItem.Format, "'" + reportItem.Value);
}
else
{
objRange.Value2 = String.Format(reportItem.Format, reportItem.Value);
}
break;
default:
objRange.Value2 = String.Format(reportItem.Format, reportItem.Value);
break;
}
}
else
{
objRange.Value2 = String.Format(reportItem.Format, reportItem.Value);
}
}
/// <summary>
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
public static void DataGridViewToExcel(MultiColHeaderDgv dgv)
{
#region
//申明保存对话框
SaveFileDialog dlg = new SaveFileDialog();
//默然文件后缀
dlg.DefaultExt = "xls ";
//文件后缀列表
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
//默然路径是系统当前路径
dlg.InitialDirectory = Directory.GetCurrentDirectory();
//打开保存对话框
if (dlg.ShowDialog() == DialogResult.Cancel)
{
return;
}
//返回文件路径
string fileNameString = dlg.FileName;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{
return;
}
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//验证以fileNameString命名的文件是否存在，如果存在删除它
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Excel.Application objExcel = new Excel.Application();
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
//申明对象
objExcel = new Excel.ApplicationClass();
//objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
//objsheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.ActiveSheet;
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
//设置EXCEL不可见
objExcel.Visible = false;
//向Excel中写入表格的表头
int displayColumnsCount = 1;
//Excel.Range
Excel.Range range1 = objExcel.get_Range(objExcel.Cells[1, 1], objExcel.Cells[3, 1]);
Excel.Range range2 = objExcel.get_Range(objExcel.Cells[1, 2], objExcel.Cells[3, 2]);
Excel.Range range3 = objExcel.get_Range(objExcel.Cells[1, 3], objExcel.Cells[3, 3]);
Excel.Range range4 = objExcel.get_Range(objExcel.Cells[1, 4], objExcel.Cells[3, 4]);
Excel.Range range5 = objExcel.get_Range(objExcel.Cells[1, 5], objExcel.Cells[3, 5]);
Excel.Range range6 = objExcel.get_Range(objExcel.Cells[1, 6], objExcel.Cells[3, 6]);
Excel.Range range7 = objExcel.get_Range(objExcel.Cells[1, 7], objExcel.Cells[3, 7]);
Excel.Range range8 = objExcel.get_Range(objExcel.Cells[2, 8], objExcel.Cells[3, 8]);
Excel.Range range9 = objExcel.get_Range(objExcel.Cells[1, 8], objExcel.Cells[1, 11]);
Excel.Range range10 = objExcel.get_Range(objExcel.Cells[2, 9], objExcel.Cells[2, 11]);
range1.Merge(0);
range2.Merge(0);
range3.Merge(0);
range4.Merge(0);
range5.Merge(0);
range6.Merge(0);
range7.Merge(0);
range8.Merge(0);
range9.Merge(0);
range9.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range10.Merge(0);
range10.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range1.NumberFormatLocal = "@"; //设置单元格格式为文本
range2.NumberFormatLocal = "@"; //设置单元格格式为文本
range3.NumberFormatLocal = "@"; //设置单元格格式为文本
range4.NumberFormatLocal = "@"; //设置单元格格式为文本
range5.NumberFormatLocal = "@"; //设置单元格格式为文本
range6.NumberFormatLocal = "@"; //设置单元格格式为文本
range7.NumberFormatLocal = "@"; //设置单元格格式为文本
range8.NumberFormatLocal = "@"; //设置单元格格式为文本
range9.NumberFormatLocal = "@"; //设置单元格格式为文本
objsheet.Cells[1, 1] = "财务分类";
objsheet.Cells[1, 2] = "项目代码";
objsheet.Cells[1, 3] = "项目名称";
objsheet.Cells[1, 4] = "项目内涵";
objsheet.Cells[1, 5] = "除外内容";
objsheet.Cells[1, 6] = "计价单位";
objsheet.Cells[1, 7] = "说明";
objsheet.Cells[2, 8] = "省定价";
objsheet.Cells[3, 9] = "三档";
objsheet.Cells[3, 10] = "二档";
objsheet.Cells[3, 11] = "一档";
objsheet.Cells[1, 8] = "价格（元）";
objsheet.Cells[2, 9] = "市定价格";
range2.ColumnWidth = 12; //设置单元格的宽度
range3.ColumnWidth = 20; //设置单元格的宽度
range4.ColumnWidth = 35; //设置单元格的宽度
range5.ColumnWidth = 25; //设置单元格的宽度
range7.ColumnWidth = 25; //设置单元格的宽度
range2.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range3.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range4.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range5.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
range7.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
Excel.Range rangGol = objsheet.get_Range("A1", "K" + dgv.RowCount.ToString());
rangGol.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; // 文本水平居中方式
rangGol.WrapText = true;
//向Excel中逐行逐列写入表格中的数据
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
//tempProgressBar.PerformStep();
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + 4, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
//保存文件
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if (objWorkbook != null)
{
objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
}
if (objExcel.Workbooks != null)
{
objExcel.Workbooks.Close();
}
if (objExcel != null)
{
objExcel.Quit();
}
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void exp_excel(bool print)
{
try
{
DataSet tmp = new DataSet();
tmp = ds.Copy();
ds.Clear();
ds.Merge(tmp.Tables[0].Select("true", "ten,dang"));
int k = 1;
foreach (DataRow r in ds.Tables[0].Rows)
{
r["stt"] = k++;
}
int be = 5, dong = 7, sodong = ds.Tables[0].Rows.Count + 6, socot = ds.Tables[0].Columns.Count - 1, dongke = sodong;
tenfile = d.Export_Excel(ds, "bcngay");
oxl = new Excel.Application();
owb = (Excel._Workbook)(oxl.Workbooks.Open(tenfile, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
osheet = (Excel._Worksheet)owb.ActiveSheet;
oxl.ActiveWindow.DisplayGridlines = true;
osheet.get_Range(d.getIndex(0) + "1", d.getIndex(0) + "1").EntireColumn.Delete(Missing.Value);
for (int i = 0; i < be; i++)
{
osheet.get_Range(d.getIndex(i) + "1", d.getIndex(i) + "1").EntireRow.Insert(Missing.Value);
}
osheet.get_Range(d.getIndex(be) + dong.ToString(), d.getIndex(socot) + sodong.ToString()).NumberFormat = "#,##0.00";
osheet.get_Range(d.getIndex(0) + "5", d.getIndex(socot) + dongke.ToString()).Borders.LineStyle = XlBorderWeight.xlHairline;
for (int i = 1; i < dong - 2; i++)
{
osheet.Cells[dong - 1, i] = get_ten(i - 1);
}
orange = osheet.get_Range(d.getIndex(0) + "1", d.getIndex(socot + 7) + (sodong + 7).ToString());
osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 5] = "Tổng cộng";
osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 6] = "Ghi Chú";
for (int i = 0; i < dsngay.Tables[0].Rows.Count; i++)
{
osheet.Cells[dong - 1, i + 5] = " " + dsngay.Tables[0].Rows[i]["ten"].ToString();
}
osheet.get_Range(d.getIndex(4) + "6", d.getIndex(dsngay.Tables[0].Rows.Count + 3) + "6").Orientation = 90;
osheet.get_Range(d.getIndex(0) + "6", d.getIndex(dsngay.Tables[0].Rows.Count + 3) + "6").RowHeight = 30;
orange.Font.Name = "Arial";
orange.Font.Size = 8;
orange.EntireColumn.AutoFit();
oxl.ActiveWindow.DisplayZeros = false;
osheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
osheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
osheet.PageSetup.LeftMargin = 20;
osheet.PageSetup.RightMargin = 20;
osheet.PageSetup.TopMargin = 30;
osheet.PageSetup.CenterFooter = "Trang : &P/&N";
osheet.Cells[1, 2] = d.Syte; osheet.Cells[2, 2] = d.Tenbv;
osheet.Cells[3, 2] = "Khoa: " + makp.Text;
osheet.Cells[1, socot] = "MS: 16D/BV-01";
osheet.Cells[2, socot] = "Số:";
orange = osheet.get_Range(osheet.Cells[1, socot], osheet.Cells[2, socot]);
orange.Font.Bold = false;
orange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
orange = osheet.get_Range(osheet.Cells[dong - 1, 1], osheet.Cells[dong - 1, dsngay.Tables[0].Rows.Count + 6]);
orange.Font.Bold = true;
orange = osheet.get_Range(d.getIndex(1) + "5", d.getIndex(1) + "6");
orange.Merge(Type.Missing);
//excelApp.get_Range("A1:A360,B1:E1", Type.Missing).Merge(Type.Missing)
orange = osheet.get_Range(d.getIndex(2) + "5", d.getIndex(2) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(3) + "5", d.getIndex(3) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(0) + "5", d.getIndex(0) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(socot) + "5", d.getIndex(socot) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(socot - 1) + "5", d.getIndex(socot - 1) + "6");
orange.Merge(Type.Missing);
orange = osheet.get_Range(d.getIndex(4) + "5", d.getIndex(socot - 2) + "5");
orange.Merge(Type.Missing);
orange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
osheet.Cells[5, 5] = "Ngày";
osheet.get_Range(d.getIndex(0) + "7", d.getIndex(socot) + "7").EntireRow.Insert(Missing.Value);
osheet.Cells[7, 1] = "A";
osheet.Cells[7, 2] = "B";
osheet.Cells[7, 3] = "C";
osheet.Cells[7, 4] = "D";
osheet.Cells[7, socot] = "E";
osheet.Cells[7, socot + 1] = "G";
orange = osheet.get_Range(d.getIndex(0) + "7", d.getIndex(socot + 2) + "7");
string s_ngay = d.Ngay_hethong;
osheet.Cells[sodong + 3, 2] = "NGƯỜI THỐNG KÊ";
osheet.Cells[sodong + 3, 6] = "KẾ TOÁN DƯỢC";
osheet.Cells[sodong + 3, 19] = "Ngày " + s_ngay.Substring(0, 2) + " tháng " + s_ngay.Substring(3, 2) + " năm " + s_ngay.Substring(6, 4);
osheet.Cells[sodong + 4, 19] = "TRƯỞNG KHOA " + makp.Text.ToUpper();
osheet.Cells[sodong + 7, 2] = "Họ tên: ";
osheet.Cells[sodong + 7, 6] = "Họ tên: ";
osheet.Cells[sodong + 8, 19] = "Họ tên: ";
orange = osheet.get_Range(osheet.Cells[sodong + 3, 2], osheet.Cells[sodong + 4, 20]);
orange.Font.Bold = true;
//orange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
orange.Orientation = 0;
//osheet.Cells[1,4]="BÁO CÁO SỬ DỤNG";
osheet.Cells[1, 4] = "THỐNG KÊ " + dsngay.Tables[0].Rows.Count.ToString() + " NGÀY SỬ DỤNG THUỐC, HÓA CHẤT,\n VẬT TƯ Y TẾ TIÊU HAO";
osheet.Cells[2, 4] = (tu.Text == den.Text)?"Ngày : " + tu.Text:"Từ ngày : " + tu.Text + " đến : " + den.Text;
orange = osheet.get_Range(d.getIndex(3) + "1", d.getIndex(socot - 2) + "2");
orange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
orange.Font.Size = 12;
orange.Font.Bold = true;
if (print)
{
osheet.PrintOut(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
else
{
oxl.Visible = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 根据模板生成Excel表(带表头);
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="strExcelName">输出excel</param>
/// <param name="strTemplateName">模板名称</param>
/// <param name="Title">表格标题</param>
public static void CreateByTemplateWithTitle(System.Data.DataTable dt, string strExcelName, string strTemplateName, string Title)
{
Excel.Application objExcel = new Excel.Application(); //EXCEL实例
Workbooks objBooks; //工作簿 集合
Workbook objBook; //工作簿
Sheets objSheets; //工作表集合
Worksheet objSheet; //工作表
Range objCells; //单元格
string strFile = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + ExcelPath + strExcelName; //下载路径
string strTemplate = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + TempletPath + strTemplateName; //模板路径
objExcel.Visible = false;
objExcel.DisplayAlerts = false;
//定义一个新的工作簿
objBooks = objExcel.Workbooks;
objBooks.Open(strTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
objBook = objBooks.get_Item(1);
objSheets = objBook.Worksheets;
objSheet = (Worksheet)objSheets.get_Item(1);
//命名该sheet
objSheet.Name = "导出数据";
objCells = objSheet.Cells;
//将数据导入到Excel中去
int intRow; //行号
int intCol; //列号
int RowNumber = 0;
int ColNumber = 0;
ColNumber = dt.Columns.Count;
RowNumber = dt.Rows.Count + 2;
//CProcessInfo info = HttpContext.Current.Session["ProcessInfo"] as CProcessInfo;
//info.Message = "excel标题生成";
//设置表名字体
Excel.Range TitleRange = objSheet.Range[objSheet.Cells[1, 1], objSheet.Cells[1, ColNumber]];
TitleRange.Font.Name = "宋体";
TitleRange.Font.Size = 16;
TitleRange.Font.Bold = true;
TitleRange.HorizontalAlignment = Excel.Constants.xlCenter;
//拼合单元格
TitleRange.Merge(Type.Missing);
//设置表名
objCells[1, 1] = Title;
//添加列标题
for (intCol = 0; intCol < dt.Columns.Count; intCol++)
{
objCells[2, intCol + 1] = dt.Columns[intCol].ToString();
}
//设置列标题字体
Excel.Range ColumTitleRange = objSheet.Range[objSheet.Cells[2, 1], objSheet.Cells[2, ColNumber]];
ColumTitleRange.Font.Name = "宋体";
ColumTitleRange.Font.Size = 12;
ColumTitleRange.Font.Bold = true;
ColumTitleRange.HorizontalAlignment = Excel.Constants.xlCenter;
////设定起始单元格位置
Excel.Range myrange = objSheet.Range[objSheet.Cells[2, 1], objSheet.Cells[RowNumber, ColNumber]];
//文本格式
myrange.NumberFormatLocal = "@ ";
//添加内容
for (intRow = 0; intRow < dt.Rows.Count; intRow++)
{
DataRow dr = dt.NewRow();
dr = dt.Rows[intRow];
for (intCol = 0; intCol < dt.Columns.Count; intCol++)
{
objCells[intRow + 3, intCol + 1] = dr[intCol].ToString();
}
//info.Message = "第" + intRow.ToString() + "数据插入";
//info.Current = info.Current + intRow;
}
//单元格自适应宽度
myrange.Columns.AutoFit();
myrange.Rows.AutoFit();
//加入表格边框
Excel.Borders pborders = myrange.Borders;
//设置左边框
pborders.get_Item(XlBordersIndex.xlEdgeLeft);
pborders.LineStyle = Excel.XlLineStyle.xlContinuous;
pborders.Weight = Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
object pObject = myrange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
////设置上边框线
pborders.get_Item(XlBordersIndex.xlEdgeTop);
pborders.LineStyle = Excel.XlLineStyle.xlContinuous;
pborders.Weight = Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = myrange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
////设置下边框线
pborders.get_Item(XlBordersIndex.xlEdgeBottom);
pborders.LineStyle = Excel.XlLineStyle.xlContinuous;
pborders.Weight = Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = myrange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
////设置右边框线
pborders.get_Item(XlBordersIndex.xlEdgeRight);
pborders.LineStyle = Excel.XlLineStyle.xlContinuous;
pborders.Weight = Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = myrange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
////设置内格竖线
pborders.get_Item(XlBordersIndex.xlInsideVertical);
pborders.LineStyle = Excel.XlLineStyle.xlContinuous;
pborders.Weight = Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = myrange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
////设置内格横边框线
pborders.get_Item(XlBordersIndex.xlInsideHorizontal);
pborders.LineStyle = Excel.XlLineStyle.xlContinuous;
pborders.Weight = Excel.XlBorderWeight.xlThin;
pborders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
pObject = myrange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
//保存到临时文件夹
objSheet.SaveAs(strFile, Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
objBook.Close(false, Type.Missing, Type.Missing);
//退出Excel，并且释放调用的COM资源
objExcel.Quit();
//回收资源
GC.Collect();
//关闭进程
//KillProcess("Excel");
//以字符流的形式下载文件
FileStream fs = new FileStream(strFile, FileMode.Open);
byte[] bytes = new byte[(int)fs.Length];
fs.Read(bytes, 0, bytes.Length);
fs.Close();
// CFile.FileDown(strFile, "1");
//Response.ContentType = "application/octet-stream";
////通知浏览器下载文件而不是打开
//string strValue = "attachment; filename=" + HttpUtility.UrlEncode(strFile, System.Text.Encoding.UTF8) ;
//Response.AddHeader("Content-Disposition", strValue);
//Response.BinaryWrite(bytes);
//Response.Flush();
////删除临时EXCEL文件
// File.Delete(strFile);
//Response.End();
}
